PostgreSQL — Using CASE inside ORDER BY Clause

So, maybe you want to use CASE … WHEN structure inside your ORDER BY clause. Maybe you want your data to be sorted by different columns. For example for a table like this:

employee_id | employee_name | employee_age | created_timestamp
--------------------------------------------------------------
     '3'    |    'John'     |     34       |'2016-06-22 19:10:25-07'

You want a SELECT query to sort the data with conditions based on a variable order_by_spec to determine whether the result will be sorted based on which employee data were created first, which were created the latest, or alphabetically by name both ascending and descending, like this:

--Assume this is inside a plpgsql function and we have this variable defined somewhere
DECLARE
order_by_spec VARCHAR;
...
SELECT * FROM employee_table
ORDER BY
                        CASE WHEN order_by_spec = 'RECENT_CREATED_DATE' THEN created_timestamp END DESC,
                        CASE WHEN order_by_spec = 'OLDEST_CREATED_DATE' THEN created_timestamp END ASC,
                        CASE WHEN order_by_spec = 'EMPLOYEE_NAME_DESC' THEN employee_name END DESC,
                        CASE WHEN order_by_spec = 'EMPLOYEE_NAME_ASC' THEN employee_name END ASC

The quirk that you might noticed is we have cannot use a single CASE when we want to deal with multiple options (DESC and ASC) since the result part of CASE structure can only hold data, not part of expressions like ASC or DESC.

However, things get more complicated when you want to do a SELECT DISTINCT with multiple ORDER BY options. For example:

SELECT DISTINCT employee_name, employee_age FROM employee_table
ORDER BY
                        CASE WHEN order_by_spec = 'EMPLOYEE_AGE_DESC' THEN employee_age END DESC,
                        CASE WHEN order_by_spec = 'EMPLOYEE_AGE_DESC' THEN employee_age END ASC,
                        CASE WHEN order_by_spec = 'EMPLOYEE_NAME_DESC' THEN employee_name END DESC,
                        CASE WHEN order_by_spec = 'EMPLOYEE_NAME_ASC' THEN employee_name END ASC

Now in that example we want to find all unique employee names with the same age. If you simply do that , you will get the following error:

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 3:                         CASE WHEN order_by_spec = 'EMPLOYEE_AGE_DESC' ...                                 ^

That error looks a bit strange, since we already included both columns we wanted to sort by in the SELECT list.

It turns out, what we have to do is repeat the CASE structure in the SELECT clause:

SELECT DISTINCT employee_name, employee_age,
  CASE WHEN order_by_spec = 'EMPLOYEE_AGE_DESC' THEN employee_age END,
  CASE WHEN order_by_spec = 'EMPLOYEE_AGE_DESC' THEN employee_age END,
  CASE WHEN order_by_spec = 'EMPLOYEE_NAME_DESC' THEN employee_name END,
  CASE WHEN order_by_spec = 'EMPLOYEE_NAME_ASC' THEN employee_name END
FROM employee_table
ORDER BY
  CASE WHEN order_by_spec = 'EMPLOYEE_AGE_DESC' THEN employee_age END DESC,
  CASE WHEN order_by_spec = 'EMPLOYEE_AGE_DESC' THEN employee_age END ASC,
  CASE WHEN order_by_spec = 'EMPLOYEE_NAME_DESC' THEN employee_name END DESC,
  CASE WHEN order_by_spec = 'EMPLOYEE_NAME_ASC' THEN employee_name END ASC

And done! Now we get a list of employees with unique name and age combination, ordered by whatever is specified in order_by_spec.