postgreSQL doesn't order by

Question

I have this postgreSQL query, which is supposed to be returning

top 3 comments ordered by comment_date from

top 10 posts table ordered by post_date...

for some reason it's not ordering by post_date:

with a as 
      (
          SELECT  
           posts.post_id as post_id 
          FROM posts where user_id = 'user1'
          order by post_date desc --this is not working
          limit 10
      ), b as
      (
          select user_id,
          comment_id,
          post_id,
          comment_date
          , ROW_NUMBER() over (partition by post_id order by comment_date desc) as RowNum
          from post_comments
      ) 
        SELECT * from a 
     INNER JOIN b USING (post_id)
      where b.RowNum <= 3

example: https://extendsclass.com/postgresql/434e9d2

in the example it should be getting the post which has the highest post_date so like this post3>post2>post1

but the ordering of posts is not working...

I'm new to postgreSQL so i don't have any idea what's happening... actually i got that query from a stackoverflow answer just edited it a little bit: Answer

Thanks for answering and sorry for my bad english

Gordon Linoff · Accepted Answer · 2021-06-10 01:32:23Z

5

The ordering is just fine. How do I know? The outer query has no order by. Without an order by the results can be in any order.

If you want results in a particular order, then you need to be explicit:

SELECT * 
FROM a JOIN
     b USING (post_id)
WHERE b.RowNum <= 3
ORDER BY a.post_date DESC;

Note: You'll need to include post_date in the a CTE.

answered Jun 10, 2021 at 1:32

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aland Sleman Over a year ago

oh wow thank you so much for the quick reply

Collectives™ on Stack Overflow

postgreSQL doesn't order by

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related