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:


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');
Oracle – Change the behavior of ORDER BY

Leave a Reply

Your email address will not be published. Required fields are marked *