Order data by # of results

[Suchy]

id |  who  | when

--+-------+------

1  | mike  | wed

2  | mike  | sat

3  | rob    | sat

4  | bob    | mon

5  | steve  | sun

6  | anna  | thu

7  | anna  | wed

8  | carl    | wed

9  | anna  | fri

10| rob    | tue

 

I would like to dislay all this data and arrange it from the person who loged-in the most to the person who loged-in the least.

The results should be:

anna, mike, rob, steve, bob, carl

 

How should I do this?

SELECT * FROM logins ORDER BY ??????

 

[PFMaBiSmAd]

You need to form groups using the who column, count how many rows are in each group, then order by the resulting counts -

 

SELECT who, COUNT(*) AS cnt FROM logins GROUP BY who ORDER BY cnt DESC

 

If you don't want the resulting counts to appear in the result set, a slightly simpler version -

 

SELECT who FROM logins GROUP BY who ORDER BY COUNT(*) DESC