SQL Selecting Top Ten Most Common

[G.Bloke]

I've a feeling that I should know the answer to this, but after a long day of work my brain isn't working as fast as it should.

I've a database of surnames and want to select the 10 most common. Can this be done with one query or will I have to do some post fetch array sorting?

Of course I know it can be done by the latter method but having mysql do it seems the preferable way.

[jason]

SELECT Surname, COUNT(*) FROM TABLENAME GROUP BY Surname;

should do the trick...

--Jason

[G.Bloke]

Aye that will still need to be sorted after fetching though to give them in the right order. However I've resigned myself to that and have just sorted the resultant array using usort.

[Andrew Chai]

SELECT TOP 10 Surname, Count(Surname)
From Tablename
Group By Surname
Order By Count(Surname) Desc;

That should work...