SQL custom sorting

sql custom sorting
Custom sorting / ordering

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.

 

tz state
EST NJ
PST CA
MST CO
CST TX
CST AL
MST AZ
EST CT
PST NV
EST NY
MST NM
CST MN
PST WA

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.
tz state sortorder
EST NJ 1
PST CA 4
MST CO 3
CST TX 2
CST AL 2
MST AZ 3
EST CT 1
PST NV 4
EST NY 1
MST NM 3
CST MN 2
PST WA 4
SELECT tz,state FROM state_tz order by sortorder

Note: This is the 150th post in this site.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: