Question 398932 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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]

Tags:

2

Answers


  1. 0

    You can use NULLS FIRST directive in `ORDER BY

    select * 
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 video_id last_watched_at
    1 [null] [null]
    3 [null] [null]
    4 4 2024-07-19 09:20:00.986968
    2 2 2024-07-24 07:20:00.986968
    5 5 2024-07-24 09:20:00.986968
    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search