user1575921 user1575921 - 7 months ago 9
SQL Question

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

Answer

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
Comments