MyNumbers table, we have the following schema:
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.
Consider these two examples:
Single numbers are 1, 4, 5, and 6. Since 6 is the largest, it's the output.
There are no single numbers, so the output is null.
Let's explore four SQL solutions, each offering a different approach to solve the problem.
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.
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.
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.
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.
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.