mysql_query

[jazzmatazz2005]

hi wpw

in the database there are multiple records for the same person.
for example

person 1 has 5 entries
person 2 has 3 entries
and so forth.

all im trying to do is get a simple count of who has the most entries from highes to lowest.

i wrote this query it works well for me but im using mysql version 5.1.33 locally.

SELECT mname, COUNT(mname) FROM votes GROUP BY mname ORDER BY COUNT(mname) DESC

the server this will run on uses a older version of mysql. im not sure which one though.

any ideas on how i can rewrite this?

[dmartin]

That looks perfectly fine for whatever version of MySQL you need...certainly on MySQL 5.0, and 4.x...

Is there a reason you think it won't work? GROUP BY and COUNT are pretty standard stuff.

As far as the query itself, you might want to group by the person ID (if there is one), rather than the name (in case 2 people have the same name like "John Smith"), otherwise no problem.

[Uncle Dog]

I'd agree with dmartin about compatibility (and ID). Have you actually tried (and failed) to run this on the live server yet or are you just assuming it will have problems?

[jazzmatazz2005]

Thanks Uncle Dog and dmartin.
i should have mentioned that detail.

I tried to run it.
The error i receive is:
Invalid use of group function

if i run it without ORDER BY COUNT(mname) DESC
it will output all the information but not in order.

fyi

the version of mysql that's live is 3.23.58

[dmartin]

Hmmm....still seems valid, but try this:

SELECT mname, COUNT(mname) as numvotes FROM votes
GROUP BY mname
ORDER BY numvotes DESC

Maybe using an alias is needed in 3.23?

[jazzmatazz2005]

thanks dmartin

That worked out fine.

I dont know why my original way didn't work.

Thanks again

[dmartin]

Glad that worked - old MySQL versions are a little quirky in terms of the SQL they support - my guess is that expressions without aliases don't work in 3.23 with a GROUP BY...who knows.

Maybe your installation can eventually upgrade to something better supported - 3.23 (and 4.0 and 4.1) have been "end of lifed".

[wige]

I believe this issue is that in older versions of MySQL you couldn't run a group function outside of the SELECT - in your case, COUNT. I think this was an optimization issue, the second count would require a new database call, whereas using an alias allows you to sort the results by the COUNT you already did in the SELECT statement.