mysql : How to sort columns based on other columns

user_14073325 0
Viewed 43


I am having a notifications table and want to sort by the read_at column as well as created_at column.

Notification Table

id read_at created_at
1 null 21-jun-2021 12:56:12
2 null 22-jun-2021 13:12:43
3 24-jun-2021 19:43:42 23-jun-2021 16:34:45
4 23-jun-2021 17:00:03 23-jun-2021 16:40:22
5 null 24-jun-2021 11:41:32
6 24-jun-2021 21:12:12 24-jun-2021 19:45:22
7 25-jun-2021 03:14:08 25-jun-2021 03:12:57
8 null 25-jun-2021 09:59:22
9 25-jun-2021 12:41:40 25-jun-2021 12:41:09
10 26-jun-2021 01:59:59 25-jun-2021 20:09:02

All I wanted is something like this : \

sort (if read_at is null then read_at desc, created_at desc else created_at desc);

The resultant table should be :

id read_at created_at
1 null 21-jun-2021 12:56:12
2 null 22-jun-2021 13:12:43
5 null 24-jun-2021 11:41:32
8 null 25-jun-2021 09:59:22
3 25-jun-2021 19:43:42 23-jun-2021 16:34:45
4 27-jun-2021 17:00:03 23-jun-2021 16:40:22
6 24-jun-2021 21:12:12 24-jun-2021 19:45:22
7 29-jun-2021 03:14:08 25-jun-2021 03:12:57
9 25-jun-2021 12:41:40 25-jun-2021 12:41:09
10 26-jun-2021 01:59:59 25-jun-2021 20:09:02

I am unable explain this very clear.

I want unread notifications at top sorted by created_at date.
If the notification are read, then the remaining should be sorted by created_at date.

Thanks

sql-null sql-order-by date sorting mysql
2 Answers

You can sort by the boolean expression read_at IS NULL descending, which will return all rows with read_at that are null at the top and then by created_at descending (or ascending like your expected results):

ORDER BY read_at IS NULL DESC, created_at DESC -- or ASC ???
user_104988280

I believe this should solve your problem.

Can't test it right now.

select id, read_at, created_at from notifications order by case when read_at is null then 1 else 0 end desc, created_at desc

user_164264750
Related