CODEWITHSUNDEEP
sqlpostgresql
user1575921
user1575921

Reputation: 1088

How to select data order by count rows

How to select data order by count rows

I want to select data order by count media_user_action rows, don't know how to do this in one query syntax

e.g
media_id 1 3 rows in media_user_action
media_id 2 1 row in media_user_action

return data
media_id 1 then media_id 2

SELECT
  m.*
  FROM media m 
  LEFT JOIN media_user_action mua ON mua.media_id = m.id

// ORDER BY COUNT(mua.*)  count rows desc ?

media

id | 
1
2

media_user_action

id | media_id | user_id | type
1  | 1        | 1       | 0
2  | 1        | 13      | 0
3  | 1        | 15      | 1
4  | 2        | 16      | 0

Upvotes: 0

Views: 60

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use COUNT in the ORDER BY clause:

SELECT m.id
FROM media m
LEFT JOIN media_user_action mua 
    ON mua.media_id = m.id
    AND mua.type = 0
GROUP BY m.id
ORDER BY COUNT(mua.id) DESC

If you want to add additional condition for media_user_action, add it in the ON clause:

SELECT m.id
FROM media m
LEFT JOIN media_user_action mua 
    ON mua.media_id = m.id
GROUP BY m.id
ORDER BY COUNT(mua.id) DESC

Upvotes: 3

Related Questions