2nd Highest Salary

Second-highest salary — and why MAX-of-everything is wrong

Return the second-highest distinct salary, or NULL if it doesn't exist. The earlier page claimed you can "use MAX to select the second item" — but a bare MAX returns the first (highest). The correct idea is "the highest salary that is strictly below the overall maximum":

-- Approach 1: max below the max (NULL-safe, no LIMIT)
SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

If everyone earns the same (or there's one row), the inner WHERE matches nothing and MAX over the empty set is NULL — exactly what the spec wants.

-- Approach 2: distinct, ordered, skip one (must wrap to yield NULL when absent)
SELECT (SELECT DISTINCT salary
        FROM Employee
        ORDER BY salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary;

The outer SELECT (subquery) wrapper is essential in approach 2: a plain ... LIMIT 1 OFFSET 1 returns no row when there's no second salary, but the spec requires a single row containing NULL. Wrapping a scalar subquery yields NULL for the empty case.

Pitfalls

• DISTINCT matters: with salaries [200, 200, 100] the second-highest is 100, not 200. OFFSET 1 on non-distinct values would wrongly return 200.
• Generalizes to "Nth highest" via DENSE_RANK() OVER (ORDER BY salary DESC) then filter = N.

Takeaways

• "Second highest" = MAX(salary) WHERE salary < MAX(salary), or distinct-ordered LIMIT 1 OFFSET 1.
• Wrap the LIMIT/OFFSET form in a scalar subquery so the empty case returns NULL.
• Use DISTINCT/DENSE_RANK so tied top salaries don't hide the real runner-up.

---

Re-authored for correctness for this guide (the prior version claimed a bare MAX yields the second value). Pattern: LeetCode 176. See also: Aggregate Functions, LIMIT and OFFSET, Window Functions.

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — 2nd Highest Salary

Why this concept exists (judgment layer)

Nth-highest with ties and NULL-if-absent is the classic ranking drill. Bare MAX is first-highest; DISTINCT/OFFSET needs a scalar wrapper for empty second.

Mental model (install this intuition)

Second highest distinct = MAX of salaries strictly below global MAX. Or ORDER BY DESC DISTINCT OFFSET 1 LIMIT 1 wrapped as scalar subquery → NULL when missing. Ties at top must not become 'second.'

Worked example with numbers or traced steps

Salaries: 200,200,100 → second distinct = 100
MAX(salary) WHERE salary < (SELECT MAX(salary)) → 100
OFFSET 1 without DISTINCT on [200,200,100] could return 200 — wrong
Single employee → MAX-below-max empty → NULL (spec)
Nth: DENSE_RANK() OVER (ORDER BY salary DESC) FILTER rn = N

When NOT to use / named alternative

If duplicates should count as separate ranks (row-number ranks), use ROW_NUMBER not DENSE_RANK/DISTINCT. If you need full top-N list, use window limit pattern not scalar max.

Failure mode & ops fingerprint

Fingerprint: returns 200 as second when two people earn 200; bare LIMIT OFFSET returns zero rows instead of NULL; interview says MAX alone without subquery.

Hostile-panel drills (defend the decision)

Q1. Why wrap LIMIT/OFFSET in SELECT (subquery)?
Model answer: Empty OFFSET yields no row; scalar subquery yields NULL column in one result row per LeetCode 176.

Q2. DENSE_RANK vs ROW_NUMBER for Nth salary?
Model answer: DENSE_RANK treats ties as same rank (distinct salary ranks); ROW_NUMBER ranks rows, so two 200s occupy rank 1 and 2.

Q3. Generalize to 4th highest distinct.
Model answer: WHERE DENSE_RANK()=4 over salary DESC, or nested MAX-below chain / OFFSET 3 with DISTINCT.

I just read a lesson on **2nd Highest Salary** (Databases) and want to truly understand it. Explain 2nd Highest Salary 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 **2nd Highest Salary** 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 **2nd Highest Salary** 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 **2nd Highest Salary** 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.