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)

Input and Output

Input table:

MyNumbers

Output column:

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:

1, 4

Largest is:

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

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:

SELECT num
FROM MyNumbers
GROUP BY num

This collapses duplicates into one row per number.

Then we count occurrences and filter:

HAVING COUNT(*) = 1

This keeps only numbers that appear exactly once.

The outer query selects the largest among them:

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.

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:

CREATE TABLE MyNumbers (
    num INT
);

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

Expected output:

num
4

Additional case:

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.