SQL: Sorting in reverse chronological order but each day in ascending chronological order-CodePudding

I have a Postgres table called media that keeps track of media files used in my app. I want to be able sort the files in reverse chronological order but have each date in ascending order. So, for example, if you have 50 photos from today and 20 from yesterday then they would be sorted this way:

Today.1
Today.2 ...
Today.49
Today.50
Yesterday.1
Yesterday.2 ...
Yesterday.19
Yesterday.20

This query accomplishes that successfully:

SELECT name, date_created
FROM (select name, date_created, date(date_created), row_number() OVER(ORDER BY date_created) row_number FROM media
ORDER BY date DESC, row_number ASC) AS results;

But I also have to do a join with the users table. Here's an abridged version of that query:

SELECT media.media_id, media.name 
FROM (SELECT media.media_id, media.name, media.date_created, date(media.date_created), row_number() OVER(ORDER BY media.date_created) row_number FROM media, users
WHERE media.user_id = users.user_id AND users.user_id = 'b14c1548-d433-4bc2-9059-9091262e7305'
ORDER BY date DESC, row_number ASC) as results;

Unfortunately I'm getting this error: ERROR: missing FROM-clause entry for table "media"

I don't understand how to resolve this. Might anyone have any suggestions?

CodePudding user response：

There is no table alias called media in your outer query. You called it results. To avoid ambiguity, you can either join with USING or give the columns different aliases. Always join with the explicit JOIN syntax!

You should not use a subquery like that. If there is no ORDER BY in your outer query, you cannot rely on a sorted result.

This should be the simplest solution:

SELECT media.media_id, media.name 
FROM media
   JOIN users USING (user_id)
WHERE users.user_id = 'b14c1548-d433-4bc2-9059-9091262e7305'
ORDER BY date(date_dreated) DESC, date_created ASC;