Let’s say i have a table conversation with random sort, like below
| created_at_one | created_at_two | message_one | message_two |
|---|---|---|---|
| March, 11 | Null | How | one |
| March, 12 | July, 2 | Fine | two |
| July, 1 | March,5 | Hallo | three |
| March, 10 | Null | Hi | four |
There is 2 created_at columns. I want to sort the row by created_at_one, but if created_at_two is not Null, then sort by created_at_two.
| created_at_one | created_at_two | message_one | message_two |
|---|---|---|---|
| March, 10 | Null | Hi | four |
| July, 1 | March, 5 | Hallo | three |
| March, 12 | July, 2 | Fine | two |
| March, 11 | Null | How | one |
My code like below, but doesn’t meet the expectation
ORDER BY message_one.created_at_one DESC NULLS LAST, message_two.created_at_two DESC
>Solution :
You can use COALESCE() to return created_at_two if is it not NULL or to return created_at_one otherwise.
SELECT *, COALESCE(create_at_two, create_at_one) AS created_at
ORDER BY created_at DESC NULLS LAST;