Usually, sorting a dataset in SQL is a simple task. Using ActiveRecord on Ruby on Rails makes it even easier. But there are times we need to dynamically calculate a value based on the row's content and use that value to sort the dataset.

This article covers using SQL CASE statements (supported by PostgreSQL and MYSQL databases and possibly more) to generate a value and sort it conditionally.

• Map column value to a sortable value
• Select sort value column based on another column value

Map column values to static values

Assume you have an Incident model with a column kind. Kind is one of "info", "warning", "exception". You need to sort incidents in the order "exception" > "warning" > "info".

We can't simply order(kind: :asc) the dataset for obvious reasons. To achieve our requirements, we will use a SQL CASE statement as such:

Incident
  .where(...)
  .order(%q(
    CASE incidents.kind
    WHEN 'exception' THEN 1
    WHEN 'warning' THEN 2
    WHEN 'info' THEN 3
    END
  ))

I believe the example is quite self-explanatory. We are assigning values based on incidents.kind value and sort by it.

Note: You may add ASC or DESCafter END should you need.

Conditionally Select Column to Sort

The above example maps the column value to a static value (ex: an exception, the value is 1). Sometimes we need to select a column based on the value of another column.

In the following example, I have a Transaction model. Based on the STI class, we need to select the date field to order.

Transaction
  .where(...)
  .order(%q(
    CASE transactions.type
      WHEN 'Transaction::Live'    THEN transactions.created_at
      WHEN 'Transaction::Batch'   THEN transactions.deposited_at
    END"
  ))

Explanation: If the row is a Transaction::Live type, the sort value should be the created_at column value.

Use IF within ORDER

You can't use IF with ORDER. This is because IF is a "control of flow" statement and CASE is an expression that returns a value. We need to use a statement returns a value within ORDER.