Dhruv Dhruv - 4 months ago 8
SQL Question

SQL: How to select the row with the maximum value and another condition?

I want to select the row with the maximum score but since there can by many rows with maximums I want the row which has the highest value for another column. Consider that the values in this column are different. How do I write an SQLite query for this. This is my idea till now:

SELECT NAME, MAX(SCORE) FROM PLAYERS ORDER BY SECOND_COLUMN;


but it doesn't seem to work. How do I do that? Please help.
Thanks.

Answer

If I understand correctly, you can do what you want using order by and limit:

select p.*
from players p
order by score desc, second_column
limit 1;

Your query doesn't work because the MAX(SCORE) makes it an aggregation query. However, there is no GROUP BY, so the NAME is a syntax error. Also, SECOND_COLUMN in the ORDER BY is not defined, because of the aggregation.

Comments