Custom order for SQL Query results using Order By

One thing that I surely believe is "Necessities drive Innovation". But, one has to think different at crucial times to be innovative. Well, that's not a subject of discussion for this. But it does originate from that idea.

Just before our monthly release and when everything is all prepared, we found a bug in the code. GOD, how many times we test, where do they make their way. Anyways, we needed something which required no code changes as the code is already delivered. We only had database with us to play.

Basically our problem was, we needed the first record from the set of two records based on a values in VARCHAR field. That means, we needed to apply some ordering to the records based on the values. But, ORDER BY only supports Alphabetic ordering on the values.

But, that's what we generally know. Because we wanted to order the records in the manner we specify, we had to look for some solution. And we did find an interesting stuff at Jmatrix.net that allows us to specify the ordering criteria in ORDER BY clause.

What we can do is, based on the values, we can give an alternate value using DECODE() function in Oracle.

So lets say you have a column for storing the status of the records for employees. The probable values are 'ACTIVE', 'INACTIVE', 'TRANSFERRED', 'BLOCKED' and lets say you want to sort the records first fetched with these statuses based on your own criteria. Say, INACTIVE should come first, then TRANSFERRED, then BLOCKED, and finally ACTIVE.

Now, normal ORDER BY wont allow that, but here is how you can do this with DECODE().

SELECT EMPNO, ENAME, SALARY, STATUS FROM EMP
ORDER BY
DECODE (STATUS, 'INACTIVE',    'a',
                'TRANSFERRED', 'b',
                'BLOCKED',     'c',
                'ACTIVE',      'd')

So, you have your custom order for the ORDER BY clause. Hope, it solves some of your requirements like mine. :-D

Note: The emp table does not contain a STATUS column as such. Its hypothetical to illustrate the example.