What is SQL Custom Sorting ?
In SQL queries mostly there will be a need to sort data either in ASC order or DESC order. There are few occasions where custom sorting / ordering is needed.
Look at this sample data. It can be sorted by Time Zone (tz) or State name in ASC / DESC.
What if it needs tobe sorted in following order EST, CST, MST, PST as it appears in from East to West of US map ?
In MYSQL its very simple, just add mention FIELD(column you need to sort, followed by data order).
mysql > SELECT tz,state FROM state_tz ORDER BY FIELD (tz,'EST','CST','MST','PST');
If you second level sorting on state names it can be added after comma (,).
mysql > SELECT tz,state FROM state_tz ORDER BY FIELD (tz,'EST','CST','MST','PST'), State;
In Microsoft SQL Server, the same can be achieved by introducing a third column called sortorder and sorting based on that.
SELECT tz,state FROM state_tz order by sortorder
Note: This is the 150th post in this site.