Custom sorting using ORDER BY
ORDER BY clause can be used to sort the results returned by SELECT statement in SQL.It orders the result set by specified column list. When used with character data type columns it sorts data in dictionary-order. Sometimes, we need result set to be sorted in a custom order, for example, a specific value must appear at top of result set, and others can be sorted in standard order.
To define the custom sort order, you can use a CASE expression in the ORDER BY clause.
for example, consider following list of cities in india.
- Delhi
- Bombay
- Bangluru
- Chennai
- Ahmedabad
- Kolkata
- Surat
- Pune
Now the normal order by with the city name ascending query
- SELECT CountryName
- FROM dbo.Country
- ORDER BY cityName ASC
Shows the result as follows.
- Ahmedabad
- Bangluru
- Bombay
- Chennai
- Delhi
- Kolkata
- Pune
- Surat
Now I want to start the list with first Delhi then Chennai and other are in ascending then my query is using Case when ... then is as follows
- SELECT cityName
- FROM city
- ORDER BY CASE WHEN cityName = 'Delhi' THEN '1'
- WHEN cityName = 'Chennai ' THEN '2'
- ELSE cityName END ASC
The Result for this query is as below
- Delhi
- Chennai
- Ahmedabad
- Bangluru
- Bombay
- Kolkata
- Pune
- Surat
Other than using case when... then we can also use the
- function FIELD for custom sorting
ORDER BY FIELD(fieldname, 'val1, 'val2',...) DESC
for above query we can use field function as follows.
- SELECT cityName
- FROM city
- ORDER BY FIELD(cityName, 'Delhi, 'Chennai') ASC
Using the above query we can produce the same result which done using case when .. then
The Result for this query using field is same.
- Delhi
- Chennai
- Ahmedabad
- Bangluru
- Bombay
- Kolkata
- Pune
- Surat