CODEWITHSUNDEEP
sqlpostgresql
Aland Sleman
Aland Sleman

Reputation: 317

postgreSQL doesn't order by

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

Upvotes: 0

Views: 123

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 5

Related Questions