Custom sorting in SQL

Really often there are some status columns stored in many different tables in the applications we are working on. Mostly they are also fundamental attributes for whole company.

I’m going to show a resolution for a well know problem in my opinion (I’ve experienced that in two different companies already) and some of the folks didn’t know the solution. So there are really often some statuses columns stored in many different tables in the applications we are working on and mostly they are also fundamental attributes, I am not afraid to say that, for whole company.

Let me give you an example. Banking industry. Reporting in investment institution – documents area. Such documents would always have a status. In my self-prepared sample would be such statuses:

• IN_PROGRESS,
• PENDING_APPROVAL,
• APPROVED,
• PUBLISHED

Now what every database admin will do is he will tell you over design phase:

– Come on guys, you store four the same Strings in millions of records in our database, could you please (this is an order in fact, not a question) map them into numbers so memory utilization will decrease 99% for that column?

– OK mate, we can do that.

You will probably create beautiful Enum in your code looking more or less like the following:

public enum ReportStatusEnum {

    APPROVED("Approved", 3),
    IN_PROGRESS("In Progress", 1),
    PENDING_APPROVAL("Pending Approval",2),
    PUBLISHED("Published", 4);

    private String guiName;
    private int dbStatus;

    ReportStatusEnum(String guiName, int dbStatus) {
        this.guiName = guiName;
        this.dbStatus = dbStatus;
    }
}

All happy? Not exactly. I would say in 99% of such cases that statuses are really relevant from the end user perspective. These statuses are core part of the application, you can’t rely on numbers as users don’t know your mapping. Of course you will map them through your enum. That’s nice. But what if they need to sort them or you need to implement lazy loading after such sorting? Even worse that sorting order is not the same as values defined on our enum. 

In such scenario the only way to achieve such functionality is usage of ORDER BY CASE statement. This is the order we need to define on my example to satisfy end users expectations:

Let’s take a look at sample data with such specification:

select * from report order by CASE
    when status = 1 then 'In progress'
    when status = 2 then 'Pending approval'
    when status = 3 then 'Approved'
    when status = 4 then 'Published'
END

After run of such query the output is present below:

CASE may be also really helpful in case of different scenarios, the one above is just the sample one.

Performance concerns:

Please note that even if ORDER BY CASE is probably good solution for such problems it may cause some slowness on the database site. Performance on my case has been always better than in the case of alternative “DECODE” function which is available in Oracle – but not available in MySQL.