sqlpostgresql

Order by the oldest relation Postgres


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]


Solution

  • 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
    

    Test the query on fiddle

    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