So I have 2 tables - Videos and Watches (with datetime). I want to order the videos by the last time watched by a given user, with the never before watched videos coming first and the rest being sorted by ascending order of the last watch.
What would be the query to do that?
An example of desired result:
Videos table has 5 videos with id 1, 2, 3, 4, 5
Views table has 3 entries, video 2 watched 2 hours ago, video 4 watched 5 days ago, video 5 watched just now
The return order should be [1, 3, 4, 2, 5]
You can use NULLS FIRST
directive in ORDER BY
select id, last_watched_at
from videos
left join (
select video_id, max(watched_at) last_watched_at from views group by video_id
) last_views on id = video_id
order by last_watched_at asc nulls first
Result:
id | last_watched_at |
---|---|
1 | [null] |
3 | [null] |
4 | 2024-07-19 09:25:07.810703 |
2 | 2024-07-24 07:25:07.810703 |
5 | 2024-07-24 09:25:07.810703 |