Get distinct values from groups in MySQL

Question

I want to get the id of the lowest points from each team (the team field). My query works but i need to make sure the following query is good enough with a large table.

I need Simplification and Optimization.

Query:

SELECT T.id from teams as T

INNER JOIN (
    SELECT MIN(T1.points) AS P FROM teams AS T1
    GROUP BY T1.team LIMIT 5
) TJOIN ON T.points IN (TJOIN.P)

GROUP BY T.team
ORDER BY T.points ASC LIMIT 5

Table teams

id	team (foreign_key)	points (indexed)
1	a	100
2	a	101
3	b	106
4	c	105
5	c	102

Result

id
1
5
3

Answer 1

I believe the query you are looking for is:

SELECT MIN(T.id)
FROM teams as T
INNER JOIN (
    SELECT team, MIN(points) AS min_points
    FROM teams
    GROUP BY team LIMIT 5
) TJOIN
    ON T.team = TJOIN.team
    AND T.points = TJOIN.min_points
GROUP BY T.team
ORDER BY T.points ASC
LIMIT 5

You need to join based on both the column being grouped by and the min value. Consider the result of your query if multiple teams had a score of 100.

Another way of doing this is to use ROW_NUMBER():

SELECT id
FROM (
    SELECT id, points, ROW_NUMBER() OVER (PARTITION BY team ORDER BY points ASC, id ASC) rn
    FROM teams
) t
WHERE rn = 1
ORDER BY points ASC
LIMIT 5