Oracle – Change the behavior of ORDER BY

There are times when you would like to order the results of a query in such a way that it cannot be accomplished by the default behavior of the order by clause. For example, let’s look at the following table:

tbl_families

FirstName	LastName	FamilyMember
Mark	Smith	child
John	Smith	husband
Susan	Smith	wife
Sophia	Dice	child
Craig	Dice	husband
Maria	Dice	wife

Let’s say you need to order this table in such a way, so that you get all the husbands first, then the wives and the children at the end. Obviously, descending or ascending ordering by the FamilyMember field will not do the job. The solution is to combine the order by clause with the Oracle’s decode clause. Here is the result:

SELECT FirstName, LastName, FamilyMember
FROM tbl_families
ORDER BY DECODE (FamilyMember, 'husband', 'A', 'wife', 'B', 'child', 'C', 'Z');