Biggest Single Number

Largest number that appears exactly once

Table MyNumbers(num) may contain duplicates. A "single number" is one that appears exactly once; return the largest such number, or NULL if none exists.

Two steps: (1) keep only values whose row-count is 1, (2) take the max of those.

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

Wrapping the subquery in MAX(...) is what makes the empty case return NULL automatically: MAX over zero rows is NULL, which the problem requires.

Traced on [8, 8, 3, 3, 1, 4, 5, 6]

num	COUNT(*)	kept by HAVING COUNT(*)=1?
8	2	no
3	2	no
1	1	yes
4	1	yes
5	1	yes
6	1	yes

Singles = {1, 4, 5, 6}; MAX = 6.

Pitfalls

• HAVING filters after grouping (on the aggregate); WHERE can't see COUNT(*). This is the canonical WHERE-vs-HAVING distinction.
• Don't use LIMIT 1 ORDER BY num DESC without the subquery — you'd need it on the de-duplicated set, and it returns no row (not NULL) when empty, failing the spec.

Takeaways

• GROUP BY num HAVING COUNT(*) = 1 isolates values occurring exactly once.
• MAX over that set returns the answer and yields NULL when the set is empty — for free.

---

Re-authored for correctness for this guide (the prior solution had a missing parenthesis and a stray semicolon and would not run). Pattern: LeetCode 619. See also: GROUP BY, HAVING, Aggregate Functions.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — Biggest Single Number

Why this concept exists (judgment chain)

Pattern: filter groups by frequency then aggregate — HAVING COUNT(*)=1 isolates singles; MAX of that set returns the answer and NULL when empty. Teaches WHERE-vs-HAVING and empty-set semantics interviewers love.

Worked example with numbers or traced steps

MyNumbers: 8,8,3,3,1,4,5,6
GROUP BY num HAVING COUNT(*)=1 → {1,4,5,6}
MAX → 6
All duplicates → subquery empty → MAX → NULL (required).
Wrong: ORDER BY num DESC LIMIT 1 on raw table ignores “single” constraint
and returns no row (not NULL) when empty.

When NOT to use / named alternative

Do not use this pattern when you need the full row for the max single value — join back or use window functions. Prefer a uniqueness constraint if business forbids duplicates entirely. Skip nested MAX when problem allows empty result as zero rows.

Failure / ops fingerprint

Fingerprint: LIMIT 1 without HAVING returns a duplicated max; empty case returns 0 rows vs NULL. Ops: N/A for drill; in analytics, watch COUNT(*)=1 on high-cardinality columns (hash agg cost).

Hostile-panel drills (defend the decision)

Q1. Why HAVING not WHERE for COUNT(*)=1?
Model answer: COUNT is an aggregate after GROUP BY; WHERE cannot see it.

Q2. Why wrap with MAX instead of ORDER BY LIMIT 1 on singles?
Model answer: MAX over empty set is NULL; LIMIT on empty returns no row — problem asks NULL.

Q3. Express with windows?
Model answer: COUNT(*) OVER (PARTITION BY num)=1 then MAX of those — heavier; HAVING form is idiomatic.

I just read a lesson on **Biggest Single Number** (Databases) and want to truly understand it. Explain Biggest Single Number from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Biggest Single Number** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Biggest Single Number** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Biggest Single Number** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.