Case in Sorting

Consider a situation where you need to display the records of employee table order by Address however you want first it should display all the records with INDIA in address field then USA then UK and after that all in sorted fashion.

You query this very easily by using case in order by clause. Here is an example:

select * from TempTable
order by (case id
when 'INDIA' then 1
when 'USA' then 2
when 'UK' then 3
end), id

Here 1,2,3 will specify the position of record with the condition India,USA and UK. It will first display the record of India, then USA then UK then rest all in sorted order.

Post Reference: Vikram Aristocratic Elfin Share