GROUP BY - sorting the resulting data

ordering by the ungrouped key

[Cotsan]

I'm trying to make a table of contributors to a feature on my site, ordered with the highest contributor first etc. To extract the info from the mysql database, I use the following:

$query=sprintf("SELECT $user_name, COUNT(*) FROM $contri_table AS GROUP BY $user_name");
$result = mysql_query($query) or die(mysql_error());

The count is how many times each username appears.
GROUP BY sorts alphabetically by username but I need the result to be sorted by number of contributions (the count). I've tried all sorts of mysql queries and php sort functions with no joy.
Any help appreciated.

[Anyango]

Hello, Welcome to WebmasterWorld.

i would rather user "ORDER BY" for sorting rather then "GROUP BY"

$query="SELECT $user_name, COUNT(*) AS mycount FROM $contri_table ORDER BY mycount";
$result = mysql_query($query) or die(mysql_error());

hope this helps

regards

[Cotsan]

Thanks Anyango. I tried this previously and have just tried again, it throws out an error: "Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause" and you can't group on the mycount either.

[Anyango]

Hey , Sorry i just realized that both mine and your queries are logically wrong. Count(*) wont return seperate values for each user_name, it will return only one value thats why it is giving this error, we have to change query. leme try...

[Cotsan]

Hmmm . .

$query="SELECT $user_name, COUNT(*) FROM $contri_table GROUP BY $user_name";
$result = mysql_query($query) or die(mysql_error());

Returns an individual count for each username, can't seem to do it any other way. I think I've been staring at the code for so long that I've missed the easy solution :/

[directrix]

In MySQL 5, I think the following does what you want:

select user_name, count(*) from table
group by user_name
order by count(*) desc;

If that doesn't work in your version of MySQL, try:

select user_name, count(*) from table
group by user_name
order by 2 desc;

[Cotsan]

Thanks a lot directrix, and of course Anyango. My version of mysql is 4.0.2x and the second suggestion has worked perfectly. I now owe my sanity to you :)

[JeffSela]

Directrix, I didn't know you could use numbers like that in 'order by'. I've always done it by giving the count column an alias, something like this:

select user_name, [b]count(*) as num_contrib[/b]
from contributions
group by user_name
order by [b]num_contrib desc[/b];

Which makes larger queries more readable, and you won't have to remember to update the number if you later add more columns.

[Cotsan]

Jeff: That just gave an error for me when I tried it earlier.

[directrix]

Jeff, I agree: referencing count(*) directly, or by an alias as in your example, is definitely better, if supported.

[JeffSela]

Cotsan: I just tried it with a real table of test data, and it seems to work fine for me. Maybe your version of MySQL is very old, but as far as I know aliases have always worked, and should work in other DBMSs too.

Here's exactly what I've just tried:

create table contributions (
 id int auto_increment primary key,
 user_name text not null
);
insert into contributions (user_name) values ('fred');
insert into contributions (user_name) values ('fred');
insert into contributions (user_name) values ('joe');

select user_name, count(*) as num_contrib
from contributions
group by user_name
order by num_contrib desc;

Since fred has more contributions, he comes first:

+-----------+-------------+
¦ user_name ¦ num_contrib ¦
+-----------+-------------+
¦ fred ¦  2 ¦
¦ joe ¦  1 ¦
+-----------+-------------+

[Cotsan]

Jeff,

Thanks a lot for your time. I'm busy this week but will have another shot next weekend. directrix's way is working but I would much rather use the method that you have outlined. The MySQL version is 4.0.22 so fairly old.