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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s