SQL query to produce multiple top 1 from single table

user_14626723 0
Viewed 47

I want to produce a query to extract a row of best results for a person during a year over various distances, whilst all the results are listed in a single table. How do I formulate a query to do this?

So the table will look like this:

Date    distance    Name    time  
2020    10          Bert    00:19:38  
2020    10          Bert    00:20:02  
2019    10          Bert    00:19:52  
2020    50          Bert    01:50:00  
2020    50          Bert    01:45:34  
2019    50          Bert    01:44:00  
2020    100         Bert    04:00:00  
2020    100         Bert    03:50:00  
2019    100         Bert    04:02:00

and the results line for Bert in 2020 needs to look like this....

year    Name    best10     best50     best100
2020    Bert    00:19:38   01:45:34   03:50:00
sql-server sql
3 Answers

Hmmm . . . use conditional aggregation

select year, name,
       max(case when distance = 10 and seqnum = 1 then time end) as best_10,
       max(case when distance = 50 and seqnum = 1 then time end) as best_50,
       max(case when distance = 100 and seqnum = 1 then time end) as best_100
from (select t.*,
             row_number() over (partition by year, name, distance order by time) as seqnum
      from t
     ) t
group by year, name;
user_11440350

You can try using conditional aggregation

select year,name,
       min(case when distance=10 then time end) as best_10,
       min(case when distance=50 then time end) as best_50,
       min(case when distance=100 then time end) as best_100
from tablename
where year=2020
group by year,name
user_69148640

Yet another option is PIVOT

Example

Select date
      ,name
      ,best10  = [10]
      ,best50  = [50]
      ,best100 = [100]
 From  YourTable src
 Pivot ( min(time) for distance in ([10],[50],[100] ) ) pvt
 Where date=2020

Returns

date    name    best10              best50              best100
2020    Bert    00:19:38.0000000    01:45:34.0000000    03:50:00.0000000
user_15700000
Related