SQL: order by how many rows there are (using COUNT?)

Question

For SMF, I'm making a roster for the members of my clan (please don't come with "You should ask SMF", because that is completely irrelevant; this is just contextual information).

I need it to select all members (from smf_members) and order it by how many permissions they have in smf_permissions (so the script can determine who is higher in rank). You can retrieve how many permissions there are by using: COUNT(permission) FROM smf_permissions.

I am now using this SQL:

SELECT DISTINCT(m.id_member), m.real_name, m.date_registered  
FROM smf_members AS m, smf_permissions AS p  
WHERE m.id_group=p.id_group  
ORDER BY COUNT(p.permission)

However, this only returns one row! How to return several rows?

Cheers, Aart

Answer 1

You need a GROUP BY. I've also rewritten with explicit JOIN syntax. You might need to change to LEFT JOIN if you want to include members with zero permissions.

SELECT m.id_member,
       m.real_name,
       m.date_registered,
       COUNT(p.permission) AS N
FROM   smf_members AS m
       JOIN smf_permissions AS p
         ON m.id_group = p.id_group
GROUP  BY m.id_member,
          m.real_name,
          m.date_registered
ORDER  BY COUNT(p.permission)