Biggest Single Number | LeetCode | MSSQL
Tags: leetcode, beginners, codenewbie, mssql
Published: 2023-05-29 17:30:00

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

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

![solution1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wdojf1qv6zakltcx1s46.png)

### Source Code 2

The second solution uses a Window Function to count the occurrences of each 'num', and selects the largest single number.

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

![solution2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vapvl1j61s7v8ytd86li.png)

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

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

![solution3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h6ja4120dk93vt53flze.png)

### Source Code 4

The fourth solution is similar to the third, but it uses a subquery instead of a Common Table Expression (CTE).

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

![solution4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/56gnl8z5w39b13c99pqg.png)

## 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](https://leetcode.com/problems/biggest-single-number/description/).

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}