SQL Query to group and rank

[Seef_1]

I am trying to create a mysql query that groups people and the ranks them. I use the following to rank them without the grouping and get that result:

SELECT rank, performance, wind,athlete,dob,prov,pos,place,date FROM
(SELECT performance, wind,name,surname,dob,prov,pos,place,date,
@curRank := IF(@prevRank = performance, @curRank, @incRank) AS rank, 
@incRank := @incRank + 1, 
@prevRank := performance,
CONCAT(name,' ',surname) AS 'athlete'
FROM men p, (
SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
) r 
WHERE year='2017' AND event='60m' 
ORDER BY performance ASC) s

+------+-------------+---------+--------------------+
| rank | performance |  wind   |      athlete       |
+------+-------------+---------+--------------------+
|    1 |       06.69 | Indoors | Emile ERASMUS      |
|    1 |       06.69 | Indoors | Emile ERASMUS      |
|    1 |       06.69 | Indoors | Emile ERASMUS      |
|    1 |       06.69 | Indoors | Emile ERASMUS      |
|    5 |       06.72 | Indoors | Emile ERASMUS      |
|    6 |       06.73 | Indoors | Emile ERASMUS      |
|    7 |       06.75 | Indoors | Emile ERASMUS      |
|    8 |       06.76 | Indoors | Emile ERASMUS      |
|    8 |       06.76 | Indoors | Ncincihli TITI     |
|    8 |       06.76 | Indoors | Ncincihli TITI     |
|   11 |       06.77 | Indoors | Ncincihli TITI     |
|   11 |       06.77 | -0.3    | Le Roux VAN TONDER |
+------+-------------+---------+--------------------+

How can I change this to get this result:

+------+-----+-------------+---------+--------------------+
| rank | Pos | performance |  wind   |      athlete       |
+------+-----+-------------+---------+--------------------+
|    1 |   1 |       06.69 | Indoors | Emile ERASMUS      |
|    2 |   8 |       06.76 | Indoors | Ncincihli TITI     |
|    3 |  11 |       06.77 | -0.3    | Le Roux VAN TONDER |
+------+-----+-------------+---------+--------------------+

[ryantroop]

You could find a way to use the DISTINCT keyword, or a group - likely the latter will have better performance. Of course, your query will have to change to meet the grouping mechanism.

However, your desired output is a bit odd in terms of data - your 1st - 8th is set for a reason, particularly that they have different performance values. So depending on what you are planning to do with your data, you may wish to consider refining your expected output to give credit where credit is due (unless the intention is just to list top performers, in which case why not just do a group by where you MIN the performance?)

Hope that helps.

Ryan

[pty]

Unfortunately, MySQL doesn't support Window Functions, which make this type of query much simpler:

select rank(), pos, performance, wind, athlete
over (
    partition by athlete, wind
    order by pos, performance
)