Search code examples
sqlselectcountsql-order-by

Ordering by COUNT() in SQL

Let's say I have a database table like this:

users
------
id
email
referrerID

How could I sort by the members with the most referrals? I was trying something along the lines of:

SELECT id, email
FROM users
WHERE 1
ORDER BY COUNT(referrerID) DESC;

But this does not seem to work. I think that the default value 0 may also be affecting this somehow.

Solution

  • Following clarification

    SELECT referrerID,
       COUNT(id) as Num
FROM   users
GROUP  BY referrerID
ORDER  BY CASE
            WHEN referrerID = 0 THEN -1
            ELSE COUNT(id)
          END DESC;