CODEWITHSUNDEEP
mysqlsql
Chad Smith
Chad Smith

Reputation: 177

query 1st, 2nd, 3rd place with tie break using 2nd and 3rd lowest with each time on one row

I am trying to get the top three fastest by ID and if there is a tie to use the 2nd and 3rd fastest times as a tie break. So in the example below 3 and 5 tied for the third fastest times but 3 has the 2nd fastest time between the two.

Preferably the results would show the 1st, 2nd and 3rd times for each id and be ordered by the 1st fastest followed by the 2nd and then 3rd fastest for each ID.

So the table looks like this:

 ID | TIME
----|-------  
 1  |  6.45  
 2  |  12.43  
 1  |  4.52  
 4  |  16.24  
 5  |  9.43  
 2  |  10.46  
 2  |  8.46  
 3  |  17.49  
 4  |  20.46  
 3  |  16.49  
 5  |  16.97  
 3  |  9.43  
 1  |  25.77

I am hitting a wall trying to get the correct result all in one query..

Desired output would look something like: 

 ID | lowest time | 2nd lowest | 3rd lowest
----|-------------|------------|------------ 
 1  |  4.25       | 6.45       | 25.77  
 2  |  8.46       | 10.46      | 12.43  
 3  |  9.43       | 16.49      | 17.49
 5  |  9.43       | 16.97      |

Upvotes: 2

Views: 1298

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52665

CREATE TABLE times  (ID int, theTime Decimal(5,2));

INSERT INTO times VALUES (1, 6.45),
(2, 12.43),  
(1, 4.52),
(4, 16.24),
(5, 9.43),
(2, 10.46),
(2, 8.46),
(3, 17.49),
(4, 20.46),
(3, 16.49),
(5, 16.97),
(3, 9.43),
(1, 25.77);


SELECT DISTINCT
    id,
    (SELECT thetime FROM times t2 WHERE t.iD = t2.id 
     order by thetime limit 1) lowesttime,
    (SELECT thetime FROM times t2 WHERE t.iD = t2.id 
     order by thetime limit 1,1) secondlowest,
    (SELECT thetime FROM times t2 WHERE t.iD = t2.id 
     order by thetime limit 2,1) thirdlowest
 FROM 
    times t
 ORDER BY
    lowesttime,
    secondlowest,
    thirdlowest
LIMIT 3   ; 



SELECT t1.id, 
       Min(t1.thetime) lowesttime, 
       Min(t2.thetime) secondlowest, 
       Min(t3.thetime) thirdlowest 
FROM   times t1 
       LEFT JOIN times t2 
         ON t1.id = t2.id 
            AND t1.thetime < t2.thetime 
       LEFT JOIN times t3 
         ON t2.id = t3.id 
            AND t2.thetime < t3.thetime 
GROUP  BY t1.id 
ORDER  BY lowesttime, 
          secondlowest, 
          thirdlowest 
LIMIT  3 ;

I think the second one will be faster but I'm not really sure,

Please note that as btilly pointed out if the ID 1 looked like this

ID| Time
--------
1 | 4.52
1 | 4.52
1 | 25.77

Then the final output would look like this for the second query which is incorrect

ID | lowesttime | secondlowest | thirdlowest
-------------------------------------------
1  | 4.52       | 25.77        | null
2  |8.46        | 10.46        | 12.43
3  |9.43        | 16.49        | 17.49

Upvotes: 5

JNK
JNK

Reputation: 65187

Try this. It will work for the first tie value. If you need more, you will need to add additional left joins and duplicate the second part of the order by.

This works in SQL Server:

SELECT r1.ID, MIN(r1.time)
FROM #r r1
LEFT JOIN #r r2 
    on r1.Time = r2.Time
    AND r1.ID <> r2.ID
GROUP BY r1.id
ORDER BY MIN(r1.Time), MIN(CASE WHEN r2.time IS NULL THEN r1.time ELSE NULL END)

Upvotes: 0

Related Questions