The Problem
In the MyNumbers
table, we have the following schema:
num |
---|
int |
There is no primary key for this table. It may contain duplicates. We are tasked with writing an SQL query to report the largest single number (a number that appears only once). If there is no single number, the query should return null.
Explanation
Consider these two examples:
Example 1:
Input:
MyNumbers
table:
num |
---|
8 |
8 |
3 |
3 |
1 |
4 |
5 |
6 |
Output:
num |
---|
6 |
Single numbers are 1, 4, 5, and 6. Since 6 is the largest, it's the output.
Example 2:
Input:
MyNumbers
table:
num |
---|
8 |
8 |
7 |
7 |
3 |
3 |
3 |
Output:
num |
---|
null |
There are no single numbers, so the output is null.
The Solution
Let's explore four SQL solutions, each offering a different approach to solve the problem.
Source Code 1
The first solution groups the table by 'num' using GROUP BY
, counts each group, and filters the groups that have a count of 1. It then selects the top one (since the groups are sorted in descending order), and if there are none, returns NULL.
SELECT ISNULL(
(
SELECT TOP 1 num
FROM MyNumbers
GROUP BY num
HAVING COUNT(*) = 1
ORDER BY num DESC
), NULL) AS num
This solution has a runtime of 601ms, beating 46.67% of other solutions.
Source Code 2
The second solution uses a Window Function to count the occurrences of each 'num', and selects the largest single number.
WITH num_counts AS (
SELECT
num,
COUNT(*) OVER(PARTITION BY num) as cnt
FROM MyNumbers
)
SELECT MAX(num) as num
FROM num_counts
WHERE cnt = 1
This solution has a runtime of 531ms, outperforming 67.91% of other submissions.
Source Code 3
The third solution is similar to the second one, but it uses the DISTINCT
keyword to avoid duplicate rows in the intermediate results.
WITH num_counts AS (
SELECT DISTINCT
num,
COUNT(*) OVER(PARTITION BY num) [cnt]
FROM MyNumbers
)
SELECT MAX(num) as num
FROM num_counts
WHERE cnt = 1
This solution has a runtime of 516ms, which is faster than 71.76% of other solutions.
Source Code 4
The fourth solution is similar to the third, but it uses a subquery instead of a Common Table Expression (CTE).
SELECT MAX(c.num) as num
FROM
(
SELECT DISTINCT
num,
COUNT(*) OVER(PARTITION BY num) [cnt]
FROM MyNumbers
) [c]
WHERE c.cnt = 1
This solution has the fastest runtime of 469ms, beating 86.46% of other submissions.
Conclusion
All four solutions achieve the correct output, albeit with different performance metrics. We can rank the solutions from best to worst based on performance: Source Code 4 > Source Code 3 > Source Code 2 > Source Code 1.
You can find the original problem at LeetCode.
For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.
Top comments (0)