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 WH开发者_开发知识库ERE 1 ORDER BY COUNT(referrerID) DESC;
But this does not seem to work. What is wrong?
I think that the default value 0 may also be affecting this somehow?
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;
精彩评论