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

Contents are not accessible

Contents are not accessible

Contents are not accessible

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
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.

  1. Select Unique Times: Start by selecting distinct times from the RaceResults table to ensure we're considering unique performances only.
  2. 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

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible