I Have a number column in my table where i have to order the records basing on the last digit of that column value.
For ex, i want all the records which ends with '2' and created by 'John' first and then remaining like below
## Number ## ## CreatedBy ##
3234445452 John
3432454542 John
3432454572 Alex
1234567890 John
3432432441 John
Thanks for your Help...
If you want to order by the last digit you can use mod(value, 10). If you really want to have those ending with 2 first (which I suppose isn't that much stranger than the rest of the requirement) then you can use a case statement within the order by clause:
with t42 as(
select 3234445452 as value from dual
union all select 3432454542 from dual
union all select 1234567890 from dual
union all select 3432432441 from dual
)
select value
from t42
order by case when mod(value, 10) = 2 then 0 else 1 end, mod(value, 10), value;
VALUE
----------
3234445452
3432454542
1234567890
3432432441
So that puts those ending with 2 first, then the remainder ordered by the last digit, so the end 'buckets' would be 2, 0, 1, 3, 4, 5, ..., from the first two arguments of the order by. Then the third argument orders the values numerically within each 'bucket', which puts 3234445452 before 3432454542.
You can put other fields in the order by, sure. You just need to have a case for the string too; this might be overkill:
with t42 as(
select 3234445452 as num, 'John' as createdby from dual
union all select 3432454542, 'John' from dual
union all select 3432454542, 'Alex' from dual
union all select 1234567890, 'John' from dual
union all select 3432432441, 'John' from dual
)
select num, createdby
from t42
order by case when mod(num, 10) = 2 then 0 else 1 end,
case when createdby = 'John' then 0 else 1 end,
mod(num, 10), num, createdby;
NUM CREATEDBY
---------- ---------
3234445452 John
3432454542 John
3432454542 Alex
1234567890 John
3432432441 John