MySQL select best (and oldest) perform per athlete, categories

I am trying to build the SQL query from following table (example):

Example of table with name "performances"

This is table with athletic performances. I want to select the best perform from this table per discipline and set of one or more categories. Each athlete should be only once in result though his best perform value is twice or more in performance table.

Here is expected result from table "performances"

Actually I have this SQL query, but from subquery join all rows with best value for athlete_id and best:

SELECT 

p.athlete_id, p.value 

FROM

(SELECT athlete_id, MAX(value) AS best FROM performances 

WHERE discipline_id = 32 AND category_id IN (1,3,5,7,9) 

GROUP BY athlete_id) f

INNER JOIN performances p 

ON p.athlete_id = f.athlete_id AND p.conversion = f.best

ORDER BY p.value DESC, p.created 

Please, how can I join only one row for each athlete, which has a oldest created attributte?

3 answers

  • answered 2018-01-14 08:45 M Khalid Junaid

    To get the single row for each athlete per discipline based on greatest value value you can do a self left join, To handle the tie case or if single athlete has more than 1 rows having same maximum value you can use case statement to pick the row with oldest date

    select a.*
    from performances a
    left join performances b
    on a.discipline_id = b.discipline_id
    and a.athlete_id = b.athlete_id
    and case when a.value = b.value
        then a.created > b.created
        else a.value < b.value
        end 
    where b.discipline_id is null
    

    DEMO

    Further you can add filter in your where clause

    and a.discipline_id = 32 
    and a.category_id IN (1,3,5,7,9)
    

    DEMO

  • answered 2018-01-14 08:45 Ofir Winegarten

    You don't have to use joins, you can do it with a window function:

    SELECT 
      p.athlete_id, 
      p.value 
    FROM 
      (
        SELECT 
          athlete_id, 
          value, 
          ROW_NUMBER() over (partition by athlete_id order by value desc, created) rowid 
        FROM 
          performances 
       WHERE 
          discipline_id = 32 AND 
          category_id IN (1,3,5,7,9) 
      ) p 
    where 
      p.rowid = 1
    

  • answered 2018-01-14 08:45 jaromirHloch

    Thank you a lot, Guys. After your answers I finally found the solution.

    SELECT r.* FROM 
    
    (SELECT p.athlete_id, p.conversion, MIN(p.created) AS created FROM
    
    (SELECT athlete_id, MAX(conversion) AS best 
    FROM performances 
    WHERE discipline_id = 32 AND category_id IN (1,3,5,7,9) 
    GROUP BY athlete_id) f
    
    INNER JOIN performances p ON p.athlete_id = f.athlete_id AND p.conversion = f.best
    GROUP BY p.athlete_id) w INNER JOIN performances r 
    
    ON w.athlete_id = r.athlete_id AND w.conversion = r.conversion 
    AND ((w.created = r.created) OR (w.created IS NULL AND r.created IS NULL))
    
    ORDER BY r.conversion DESC, r.created