MySQL Database Question

nca

Hi, could you guys please tell me how I would write a mysql query to return "USER1" as having the most entries in the table. Here is an example layout of the table...

NAME : DATA

USER9 : CAT
USER1 : DOG
USER1 : PIG
USER4 : GOAT

USER1 : HORSE

I just need to know what name has the most rows in the table.

Thanks!

TheDefender

Somethin like:

SELECT count(NAME) as namecount FROM table ORDER BY namecount ASC

This will count them all and order them by that namecount. You can then just output the info as needed.

nca

ok...

SELECT count(Name) as namecount FROM track ORDER BY namecount ASC

Will return a number, I beleive it is the number of total rows. I need to know which "Name" has the most entries in the entire table, and then output that "Name"

.... and other ideas?

TheDefender

SELECT *,count(Name) as namecount FROM track ORDER BY namecount ASC GROUP BY name

(I believe this is what you are looking for... Sorry for the oversight. This isn't tested, but I believe, off the top of my head, that this is how you'd go about it.)

nca

SQL-query: Documentation

SELECT * , count(Name) AS namecount
FROM track
ORDER BY namecount ASC
GROUP BY Name

MySQL said: Documentation
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY Name' at line 1

tsinka

Hi,

change the query a little bit

SELECT * , count(Name) AS namecount
FROM track
GROUP BY Name

ORDER BY namecount ASC

First GROUP then ORDER ...

Thomas

TheDefender

D'OH! Sorry about the oversight nca. Thanks for the followup Thomas.

nca

hmmm... this does not return the name that has the most rows within the table. The query you gave me returns 444 rows with all info in each row... any other thoughts???

tsinka

SELECT name, count(Name) AS namecount
FROM track
GROUP BY Name
ORDER BY namecount DESC
LIMIT 1

That should return the name and count for the name with the most rows.

But it will return only one name even if there are two names that have the same number of rows.

But I think that can be done with a slightly different query.

Thomas