Posts Tagged ‘sorting data in custom order’

As we know ORDER BY clause used to sort result as per specified order – where it may be ASC or DESC. Its sort the result set by specified columns. Its all depends on columns data type.

But in practical environment, sometimes we need result set in a specific order, for example some values should be always on top and its does not matter what are these values, but it should be on top of result set. For example here are some indian cities listed in ASC order as

City Name
Ahmadabad
Banglore
Bhopal
Chennai
Gorakhpur
Jaipur
Kolkatta
Lucknow
Mumbai
Nainital
New Delhi
Pune

Now we want New Delhi and Mumbai always on top in List, The syntax for same will be as

SELECT CityName FROM Table1
ORDER BY CASE WHEN CityName =‘New Delhi’ THEN ‘1’
              WHEN CityName ‘Mumbai’ THEN   ‘2’
              ELSE CityName END ASC

CityName
New Delhi
Mumbai
Ahmadabad
Banglore
Bhopal
Chennai
Gorakhpur
Jaipur
Kolkatta
Lucknow
Nainital
Pune

Happy reading!!!