Database Administration
postgresql query order-by
Updated Thu, 01 Sep 2022 14:07:48 GMT

Postgres order by with multiple columns


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.




Solution

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.





Comments (1)