Order By Row Count of Column? (Popularity)

[drath]

I was wondering if this type of thing was possible. Basically I want to be able to order by results by the highest row count of a column of a value. Basically, the most "popular" in a column. To give a better visual:

 

1 | apple

2 | apple

3 | orange

4 | apple

5 | orange

6 | banana

7 | apple

 

Because apple has the highest row count value, I would want to order result to be: apple, orange, banana using a single (or multiple) PHP MYSQL queries. Here's my pseudo code (that wouldn't anyways, since you can't order by DISTINCT, let alone count a column):

 

SELECT column1, column2 FROM table ORDER BY DISTINCT count(column2)

[Zane]

SELECT COUNT(column) as theCount, column FROM table ORDER by theCount

 

You could also GROUP BY theCount and get only three results...

 

3 |  apple

5 |  strawberry

19|  orange

[drath]

That doesn't seem to be working in my implementation, it doesn't seem like it is getting the "gamename" properly:

 

	
$sql = "SELECT count(gamename) as theCount FROM highscores ORDER BY theCount DESC LIMIT 2";
$result = mysql_query($sql) or die(sql_error($sql));
while ($row = mysql_fetch_array($result)) {
$gamename = $row['gamename'];
echo "<div class=\"game\"><a href=\"$gamename.php\"><img src=\"$gamename.gif\"</a>$gamename</div>";	
}

 

Shows one empty DIV. Your other method of "GROUP BY" doesn't seem to work either, giving me the error that you can't GROUP BY theCount. I tried to play around with it some more and came up with:

 

$sql = "SELECT gamename, count(gamename) as theCount FROM highscores ORDER BY theCount LIMIT 2";

 

This one at least displays some items in the DIV, but they seem to be just the two first entries in the column, not the most popular.

 

[JustLikeIcarus]

So you want to display each distinct gamename ordered by the number of times that gamename exists? Is that right?

 

Try this:

 

SELECT gamename FROM (
    SELECT gamename, count(gamename) as theCount 
    FROM highscores 
    GROUP BY gamename
    )
ORDER BY theCount DESC

[drath]

That seems to throw the "Every derived table must have its own alias" error. I am not sure exactly what it wants me to change. Thanks for that though, I wasn't even aware that you could select within a select... very interesting.

[JustLikeIcarus]

That seems to throw the "Every derived table must have its own alias" error. I am not sure exactly what it wants me to change. Thanks for that though, I wasn't even aware that you could select within a select... very interesting.

 

Oh sorry, here

 

SELECT t1.gamename FROM (
    SELECT gamename, count(gamename) as theCount
    FROM highscores
    GROUP BY gamename
    ) t1
ORDER BY t1.theCount DESC

[drath]

Ah, yes, I see what you did there. This works perfectly as intended, thanks for the work and teaching me some new tricks.