# LeetCode 619: Biggest Single Number

## Problem Restatement

We are given a table `MyNumbers`.

Each row contains a single integer.

| Column | Type | Meaning |
|---|---|---|
| `num` | int | A number |

We need to find the largest number that appears exactly once in the table.

If no such number exists, return `NULL`.

The official problem asks for the largest single occurrence number from the table. ([leetcode.com](https://leetcode.com/problems/biggest-single-number/?utm_source=chatgpt.com))

## Input and Output

Input table:

```sql
MyNumbers
```

Output column:

```sql
num
```

Output rules:

| Condition | Output |
|---|---|
| A number appears exactly once | Consider it |
| Pick the largest such number | Final answer |
| No such number | NULL |

## Example

Input:

| num |
|---:|
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 5 |

Frequencies:

| num | count |
|---:|---:|
| 1 | 1 |
| 4 | 1 |
| 3 | 2 |
| 5 | 2 |
| 8 | 2 |

Single-occurrence numbers:

```text
1, 4
```

Largest is:

```text
4
```

Output:

| num |
|---:|
| 4 |

## First Thought: Count and Filter

We need to group numbers and count how many times each appears.

Then filter to only those with count = 1.

Finally take the maximum.

## Key Insight

This is a classic aggregation problem using:

| SQL feature | Purpose |
|---|---|
| GROUP BY | Count occurrences |
| HAVING | Filter groups |
| MAX | Pick largest valid value |

We do not need joins or window functions.

## Algorithm

Step 1: Group numbers.

Step 2: Count frequency of each number.

Step 3: Keep only numbers with frequency = 1.

Step 4: Select maximum among them.

If no rows remain, result is `NULL`.

## SQL Solution

```sql
SELECT
    MAX(num) AS num
FROM (
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(*) = 1
) AS single_numbers;
```

## Code Explanation

First we group numbers:

```sql
SELECT num
FROM MyNumbers
GROUP BY num
```

This collapses duplicates into one row per number.

Then we count occurrences and filter:

```sql
HAVING COUNT(*) = 1
```

This keeps only numbers that appear exactly once.

The outer query selects the largest among them:

```sql
MAX(num)
```

If the subquery returns no rows, `MAX` automatically returns `NULL`, which matches the problem requirement.

## Correctness

Each number is grouped exactly once by `GROUP BY num`.

The `HAVING COUNT(*) = 1` condition ensures only numbers with exactly one occurrence remain.

Therefore, every value passed to the outer query is guaranteed to be a single-occurrence number.

The outer query selects the maximum of these valid values, ensuring that the returned number is both unique and the largest among all such numbers.

If no number satisfies the uniqueness condition, the subquery is empty. In that case, `MAX` over an empty set returns `NULL`, which correctly represents the absence of any valid answer.

Thus, the query returns exactly the largest number that appears once, or `NULL` if none exists.

## Complexity

Let `n` be the number of rows in `MyNumbers`.

| Metric | Value | Why |
|---|---:|---|
| Time | `O(n)` | One pass grouping + aggregation |
| Space | `O(k)` | Stores distinct numbers where `k ≤ n` |

The grouping step dominates, but remains linear in practice.

## Alternative: Window Function

We can also use `COUNT(*) OVER`.

```sql
SELECT MAX(num) AS num
FROM (
    SELECT
        num,
        COUNT(*) OVER (PARTITION BY num) AS cnt
    FROM MyNumbers
) t
WHERE cnt = 1;
```

This version keeps row-level structure before filtering.

## Testing

Sample data:

```sql
CREATE TABLE MyNumbers (
    num INT
);

INSERT INTO MyNumbers VALUES
(8),(8),(3),(3),(1),(4),(5),(5);
```

Expected output:

| num |
|---:|
| 4 |

Additional case:

```sql
TRUNCATE TABLE MyNumbers;

INSERT INTO MyNumbers VALUES
(10),(10),(20),(20);
```

No single-occurrence numbers exist, so:

| num |
|---|
| NULL |

This confirms correct handling of empty result sets.