开发者

Ordering by COUNT() in SQL

开发者 https://www.devze.com 2023-04-06 13:16 出处:网络
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:

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;  
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号