Postgres order by with multiple columns

All we need is an easy explanation of the problem, so here it is.

So imagine I have a result set that looks something like this:

+----+------------+------------+
| Id |  Date_One  |  Date_Two  |
+----+------------+------------+
|  1 | 2022-05-12 | null       |
|  2 | 2022-05-13 | 2022-05-11 |
|  3 | null       | 2022-05-14 |
+----+------------+------------+

I would like to order the result based on earliest date first, regardless of column. Each row will have either Date_One or Date_Two populated. They will never both be null.

So the result for the above result set would be:

+----+------------+------------+
| Id |  Date_One  |  Date_Two  |
+----+------------+------------+
|  2 | 2022-05-13 | 2022-05-11 |
|  1 | 2022-05-12 | null       |
|  3 | null       | 2022-05-14 |
+----+------------+------------+

(Since Id 2 has Date_Two earlier than Id 1’s Date_One).

I’m not sure how I would create my ORDER BY clause. My naive solution was to use COALESCE, but that wouldn’t work for ID 2 in my example.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Simply use order by least(date_one, date_two)

The least function returns the lowest of two values, Coincidentally, you may use greatest(date_one, date_two) to get the highest value. This also works with null, however if both values are null obviously either of these functions will return null since there is nothing to compare.

Keep in mind that using least or greatest will also make any indexes you’ve created for ordering these 2 columns unusable unless you create it as an index over an immutable function using least/greatest. Although I am not sure that’s the best move here in your scenario.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply