Customising your sort order

Ever needed to order your records based on a specific ordering? Here’s how to do it…

Let’s say you have some records which track “state”. Values for “state” can be the following:

• pending
• submitted
• accepted
• rejected

Easy enough. However, what if you want to display the results with the following ordering:

• rejected
• pending
• submitted
• accepted

As it turns out, this is pretty easy. Here’s the sql statement to do just that:

select * from things
order by 
    case state
      when 'rejected' then 1
      when 'pending' then 2
      when 'submitted' then 3
      else 99
    end

Simples.