4

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.

Improve this question

1 Answer 1

Reset to default
7

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.

Improve this answer
1
  • Thanks! Worked like a charm Commented Jul 1, 2022 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.