Query gone wrong

Anon

Hello.
I have a DB and I want to display the person (name) who has the highest amount of flags scored.
All the info is already in the DB but I am having trouble retrieving it.
Could someone please help me out a lil bit with the query?

"SELECT name, flags FROM scores WHERE flags = max(flags)";

That doesn't seem to work...could someone please, correct my error ridden query lol thank you.

Anon

Something like:

SELECT name, flags FROM scores ORDER BY flags DESC LIMIT 1, 1

should do it.
Best regards,

King Louie

amcgrath

King's right, but another option (I would go his way, but to at least see another way) would be:

"SELECT name, flags FROM scores WHERE flags = (select max(flags) from score)";

tomhath

That would get you the second highest flag. Offset is zero based (number of rows to skip before starting to read).

SELECT name, flags FROM scores ORDER BY flags DESC LIMIT 0, 1

tomhath

Can two names have the same number of flags? Kings code (once corrected) will arbitrarily pick a winner from all the tied scores. This will return all ties (assuming the code that executed the query can handle more than one row and the DBMS can do subselects).