0% completed
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
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 | +------------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible