1

How to do I order rows based on two columns, where you first order by column1, but if rows have the same value (tie), then order those by column2?

id | likes | date
---+-------+-----
0      0     203
1      8     100
2      4     24
3      1     340
4      4     262
5      4     82

So for the example table above, ordering based on likes first, then for the 3 rows that have 4, order based on date DESC?

NOTE: date has just arbitrary unix timestamp values

EDIT: This is the actual query that I am working with:

SELECT * FROM (SELECT posts.id, posts.topic_id, topics.name, 
posts.author_id, users.username, users.profile_pic, posts.date_posted, 
posts.body, posts.coordinates, (SELECT COUNT(*) FROM post_likes WHERE 
post_likes.post_id = posts.id) AS num_likes, 
ROW_NUMBER () OVER (ORDER BY (SELECT COUNT(*) FROM post_likes WHERE 
post_likes.post_id = posts.id) DESC, posts.date_posted DESC) AS RowNum 
FROM posts INNER JOIN users ON posts.author_id = users.id INNER JOIN 
topics ON posts.topic_id = topics.id WHERE author_id = 'foo') AS 
RowConstrainedResult WHERE RowNum > 0 AND RowNum <= 20 ORDER BY RowNum
Michael Hsu
  • 950
  • 1
  • 9
  • 25

1 Answers1

2

You would order it to be as 

<
select 
 from table
>
order by likes desc
         , date desc
George Joseph
  • 5,842
  • 10
  • 24