Brendan Mullan Brendan Mullan - 2 months ago 7
MySQL Question

Select rows with highest values where 1 column is unique

I have a table of data like so

---------------------
Name | Image | Points
---------------------
Bob | a.jpg | 100
Bob | b.jpg | 56
Sal | c.jpg | 87
Jim | d.jpg | 34
Bet | e.jpg | 23
Bren | f.jpg | 5
Bren | g.jpg | 15
Test | h.jpg | 10


What i want to do is select 5 rows with the highest "Points" but dont take duplicate "Names" into account.

Just selecting the highest 5 would return




Name | Image | Points
---------------------
Bob | a.jpg | 100
Sal | c.jpg | 87
Bob | b.jpg | 56
Jim | d.jpg | 34
Bet | e.jpg | 23


But i do not want to include duplicate rows, therefore the second Bob in the above would be removed and "Bren | g.jpg | 15" would be added instead

Any help is appreciated thank you

Answer

You can use NOT EXISTS() :

SELECT * FROM YourTable t
WHERE NOT EXISTS(SELECT 1 FROM YourTable s
                 WHERE t.name = s.name AND s.points > t.points)
ORDER BY t.points DESC
LIMIT 5