CODEWITHSUNDEEP
sqlsqlite
TheRandomGuy
TheRandomGuy

Reputation: 337

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.

Upvotes: 0

Views: 1213

Answers (3)

apokryfos
apokryfos

Reputation: 40730

You can do the following:

SELECT NAME, SCORE 
FROM PLAYERS 
WHERE SCORE>=(SELECT MAX(SCORE) FROM PLAYERS)
ORDER BY SECOND_COLUMN DESC
LIMIT 1

This would get all names with the same score then order by the second column and only return the maximum one

Upvotes: 1

M. Grue
M. Grue

Reputation: 331

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 2

Related Questions