Hi

I have a simple table:

Person {firstname, surname, age, hair color}.

How do I query for the most popular firstname?

ive got:
this displays all the firstnames and the number of times it appears which is fine, but i just want the most submitted firstname to appear. any ideas? would appreciate if somebody could direct me into the right direction.
thanks

Try adding this after your group by clause:

order by count(firstname) desc limit 1

thanks for reply.
but if i add that it says the following:
#1111 - Invalid use of group function

What version are you using? I'm not sure whether pre 5.0.x version support group by or limit. Thats before my time. I've gotta run now, but check the syntax for the select statement from the manual for your version to see if you can do this.

I even ran a test on my dev server:

mysql> select name, count(*) from jd_test group by name order by count(*) desc limit 1
-> ;
+----------+----------+
| name | count(*) |
+----------+----------+
| whatever | 3 |
+----------+----------+
1 row in set (0.04 sec)

mysql> select * from jd_test ;
+------+----------+
| id | name |
+------+----------+
| 1 | whatever |
| 1 | whocares |
| 1 | ido |
| 1 | whatever |
| 1 | whatever |
| 1 | whocares |
+------+----------+
6 rows in set (0.00 sec)

mysql>

nope saying the message, think it may be because im using mysql version 4.1. but surely there must be an alternative for me

version 4.1 does support GROUP BY and LIMIT

please show your exact query

I haven't used 4.1 but the online manual for that says you can use limit / group by.

Try qualifying the "count" column:

SELECT firstname, count(firstname) as FirstNameCount
FROM person
GROUP BY firstname
order by FirstNameCount desc limit 1


This works on my 5.1 server.

thanks qualifiying the column seemed to work. The query works but..

it returns Daniel as the most popular as it appears 3 times in that column,
however the names John and Lee also appear 3 times. is it going to be more complex to return all the popular names not just the first one by alphabetical order?
thanks again

the answer is yes, it is

too bad mysql's LIMIT does not work the same as microsoft's TOP n WITH TIES

try this --

hmm, I think you may have understood and i didnt explain it very well :P
I just changed the limit to 3 instead of 1. does what i need it to now.
thanks for help guys.

may i ask why you chose LIMIT 3?

because there are 3 names that are equally popular in the table. out of interest what was that query trying to achieve that you posted above?

what if there are four names that are the most popular? your LIMIT 3 query will be wrong

out of interest, the query i gave will produce the most popular names whether there is one or many with the same top score

ohh i see, that does make a whole lot more of sense
however , when i attempt to run that query im given this error:
#1248 - Every derived table must have its own alias

which i found out that means each subquery must contain unique column names.
I tried changing the names but still no luck. has it got something to do with "names" on the 2nd line of the query.

no, actually, that message refers to subqueries acting as derived tables

i've edited my previous post and added the table alias in red