Biggest Single Number | LeetCode | MSSQL

The Problem

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

Explanation

Consider these two examples:

Example 1:
Input:

MyNumbers table:

Output:

Single numbers are 1, 4, 5, and 6. Since 6 is the largest, it's the output.

Example 2:
Input:

MyNumbers table:

Output:

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.

ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai