Postgres - Conditional Order By

Aug 25, 2019 in DATABASES
postgres
3 min read

In last post, I wrote about ordering by date descending and then time ascending. Building on top of that post, I’ll make a case for conditional order by. This is something that I’ve found in few places in our codebase.

The idea is simple, use CASE in ORDER BY statement, where we can check multiple columns. We’ll use two columns this time, first column will be date_created for meta information, and second will be date_of_activation which represents when the user activated some feature. We want to see what user activated that feature, and which one didn’t, but in chronological order, so we can see all of them.

I’ll create a database and a table for example:

CREATE DATABASE conditional_order_by_sample;

-- connect to that database

-- and add extension for generating id using UUID
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sample (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  date_created TIMESTAMP WITH TIME ZONE DEFAULT now(),
  date_of_activation TIMESTAMP WITH TIME ZONE,
  username TEXT
);

We’ll populate the table with some dummy data:

INSERT INTO sample (date_created, username) VALUES (now() - '1 hour'::INTERVAL,'1');
INSERT INTO sample (date_created, username) VALUES (now() - '3 hours'::INTERVAL,'2');
INSERT INTO sample (date_created, username) VALUES (now() - '5 hours'::INTERVAL,'3');
INSERT INTO sample (date_created, username) VALUES (now() - '7 hours'::INTERVAL,'4');
INSERT INTO sample (date_created, username) VALUES (now() - '8 hours'::INTERVAL,'5');
INSERT INTO sample (date_of_activation, username) VALUES (now() - '1 hour 30 minutes'::INTERVAL,'6');
INSERT INTO sample (date_of_activation, username) VALUES (now() - '2 hour'::INTERVAL,'7');
INSERT INTO sample (date_of_activation, username) VALUES (now() - '3 hour 25 minutes'::INTERVAL,'8');

Let’s see what we’ve got:

SELECT * FROM sample;

+--------------------------------------+-------------------------------+-------------------------------+------------+
| id                                   | date_created                  | date_of_activation            | username   |
|--------------------------------------+-------------------------------+-------------------------------+------------|
| acbed25c-cd0e-4dc0-9186-01c4430973a5 | 2019-08-24 20:58:25.306949+02 | <null>                        | 1          |
| 9fbeddd8-2ba3-4607-beae-6d27e43c129f | 2019-08-24 18:58:25.308472+02 | <null>                        | 2          |
| 85de0733-227b-4e2f-9be6-7bac6112b973 | 2019-08-24 16:58:25.309081+02 | <null>                        | 3          |
| 88061762-873a-47df-9b96-8b0d1a3d7a5c | 2019-08-24 14:58:25.309646+02 | <null>                        | 4          |
| b2441768-3138-4fe1-847d-df0c0b6bceba | 2019-08-24 13:58:25.310157+02 | <null>                        | 5          |
| 77eaa375-551b-4fde-9f6e-79cc705abf4c | 2019-08-24 21:58:25.310658+02 | 2019-08-24 20:28:25.310658+02 | 6          |
| e89caa2d-e75e-4cbc-838d-3424b882b6f8 | 2019-08-24 21:58:25.311192+02 | 2019-08-24 19:58:25.311192+02 | 7          |
| 5624d78a-5471-4858-9942-ce95d6f27bac | 2019-08-24 21:58:25.311672+02 | 2019-08-24 18:33:25.311672+02 | 8          |
+--------------------------------------+-------------------------------+-------------------------------+------------+

We see that date_of_activation is somewhere null, and somewhere populated. We’ll check for date_of_activation to see how we should order. Generally speaking, the desired outcome is sort by date_of_activation if exists, and if not, use date_created. Both should be in descending order since we want to see the chronological order. We’ll also output only the column that is ordered by.

SELECT id, 
       username,
       (CASE 
          WHEN date_of_activation IS NULL THEN date_created 
          ELSE date_of_activation 
        END) AS date,
       (CASE
          WHEN date_of_activation IS NULL THEN FALSE
          ELSE TRUE
        END) AS is_activated
FROM sample 
ORDER BY
CASE 
  WHEN date_of_activation IS NULL THEN date_created 
  ELSE date_of_activation 
END DESC;

+--------------------------------------+------------+-------------------------------+----------------+
| id                                   | username   | date                          | is_activated   |
|--------------------------------------+------------+-------------------------------+----------------|
| acbed25c-cd0e-4dc0-9186-01c4430973a5 | 1          | 2019-08-24 20:58:25.306949+02 | False          |
| 77eaa375-551b-4fde-9f6e-79cc705abf4c | 6          | 2019-08-24 20:28:25.310658+02 | True           |
| e89caa2d-e75e-4cbc-838d-3424b882b6f8 | 7          | 2019-08-24 19:58:25.311192+02 | True           |
| 9fbeddd8-2ba3-4607-beae-6d27e43c129f | 2          | 2019-08-24 18:58:25.308472+02 | False          |
| 5624d78a-5471-4858-9942-ce95d6f27bac | 8          | 2019-08-24 18:33:25.311672+02 | True           |
| 85de0733-227b-4e2f-9be6-7bac6112b973 | 3          | 2019-08-24 16:58:25.309081+02 | False          |
| 88061762-873a-47df-9b96-8b0d1a3d7a5c | 4          | 2019-08-24 14:58:25.309646+02 | False          |
| b2441768-3138-4fe1-847d-df0c0b6bceba | 5          | 2019-08-24 13:58:25.310157+02 | False          |
+--------------------------------------+------------+-------------------------------+----------------+

We can now see chronologically which users activated the feature, and which ones did not.

Notice the position of DESC. It’s not in THEN part of CASE. It’s outside, since CASE only lets us choose what column would be used.