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]

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:

Do a left join of videos to views, collecting the max timestamp, and then do an ascending sort with nulls first on that column.

Setup: (next time please post a similar setup in your questions to save time)

=# create table videos(id integer);
CREATE TABLE
=# insert into videos(id) values (1), (2), (3), (4), (5);
INSERT 0 5
=# create table views(video_id integer, at timestamp);
CREATE TABLE
=# insert into views(video_id, at) values (2, now()::timestamp - interval '2 hours'), (4, now()::timestamp - interval '5 days'), (5, now()::timestamp);
INSERT 0 3

Query:

=# select videos.id, max(views.at) as last_watched_at
from videos
left join views on videos.id = views.video_id
group by videos.id
order by last_watched_at asc nulls first;
 id |      last_watched_at
----+----------------------------
  1 |
  3 |
  4 | 2024-07-19 14:47:05.241536
  2 | 2024-07-24 12:47:05.241536
  5 | 2024-07-24 14:47:05.241536
(5 rows)

You can select your videos, left joined with watches, grouping by videos.id and then order by watches.id being null descendingly as the first sort dimension and by watch time as the second sort dimension:

SELECT videos.*, MAX(watches.watch_time)
FROM videos
LEFT JOIN watches
ON videos.id = watches.video_id
GROUP BY videos.id
ORDER BY (watches.id IS NULL) DESC, MAX(watches.watch_time)

The other two answers are using a single dimension with NULLS FIRST which is of course correct, yet, the approach here is an alternative that:

• will work at other RDBMSs
• shows you how you can sort by multiple criteria