3

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.

1 Answer 1

6

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.