20. Second Fastest Time in Races (Medium)

Grokking SQL for Tech Interviews

Ask Author

Back to course home

0% completed

Vote For New Content

20. Second Fastest Time in Races (Medium)

Table of Contents

Contents are not accessible

Problem Statement

Table: RaceResults
Each row in this table records the time achieved by a participant in a sprint race, including their unique ID and the time they recorded.

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| time        | int  |
+-------------+------+
id is the primary key for this table.
time is recorded in seconds and represents the performance of a participant in a race.

Develop a solution to find the second fastest time (or second smallest time) in the RaceResults table. If there is no second fastest time (e.g., in case of only one participant), the result should be null.

Example

Input:

RaceResults table:
+----+------+
| id | time |
+----+------+
| 1  | 60   |
| 2  | 55   |
| 3  | 58   |
| 4  | 59   |
+----+------+

Output:

+------------------+
| SecondFastestTime|
+------------------+
| 58               |
+------------------+

In the example, the second fastest time is 58 seconds.

Try It Yourself

MYSQL

. . . .

Solution

To solve this problem, we will identify the second fastest time from the RaceResults table using SQL queries, handling cases where a second fastest time may not exist.

Select Unique Times: Start by selecting distinct times from the RaceResults table to ensure we're considering unique performances only.
Order Times and Select Second: Order the unique times in ascending order and use a limit clause to select the second fastest time, if available.

SQL Query

The definitive SQL query to find the second fastest time is as follows:

SELECT MAX(time) AS SecondFastestTime
FROM (
    SELECT time
    FROM RaceResults
    GROUP BY time
    ORDER BY time ASC
    LIMIT 2
) AS TopTwoTimes

Step by Step Approach

Step 1: Select Unique Times

Extract unique times from the race results to ensure we're working with distinct performances.

SELECT time
FROM RaceResults
GROUP BY time

Output After Step 1:

+------+
| time |
+------+
| 60   |
| 55   |
| 58   |
| 59   |
+------+

Step 2: Order Times and Limit

Order the times in ascending order and limit the selection to the top 2. This will give us the 2 smallest times.

ORDER BY time ASC
LIMIT 2

Final Output:

+------------------+
| SecondFastestTime|
+------------------+
| 58               |
| 59               |
+------------------+

Step 3: Select Second

Use the MAX function to select the second item from this subset, ensuring that if there's only one time, the result will be null.

SELECT MAX(time) AS SecondFastestTime

Final Output:

+------------------+
| SecondFastestTime|
+------------------+
| 58               |
+------------------+

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible