SQL Interview Prep · Lesson

Second Highest Salary, Five Ways

Subquery, LIMIT/OFFSET, and window-function solutions compared.

Second Highest Salary, Five Ways is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Question Everyone Gets

"Find the second highest salary" is the single most-asked SQL interview question. Interviewers love it because it has many correct answers and several subtle traps.

Assume an employee table with columns id and salary. Your job: return the second highest distinct salary value.

If salaries are 300, 200, 200, 100, the answer is 200, not the second row.
If there is no second distinct salary, the expected answer is usually NULL.

Over the next scenes we will solve it five different ways and discuss when each shines.

CREATE TABLE employee (
  id     INT PRIMARY KEY,
  salary INT
);

Way 1: MAX of values below the MAX

The most intuitive solution: the second highest salary is the largest salary that is strictly less than the overall maximum.

This reads almost like English and works in every SQL dialect. The inner subquery finds the top value, and the outer MAX finds the biggest value below it.

Bonus: if there is no second distinct salary, the outer MAX aggregates zero rows and returns NULL automatically. That free NULL is exactly what interviewers want.

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Why the subquery handles duplicates

Notice we never used DISTINCT in Way 1, yet duplicates are handled correctly.

If three people earn 200 and the top earner makes 300, the inner query returns 300. The outer filter keeps every row under 300, and MAX of those is 200 regardless of how many 200s exist.

This is the key insight: aggregates collapse duplicates for you. Many candidates over-engineer with DISTINCT when the aggregate already does the right thing.

Way 2: LIMIT with OFFSET

In MySQL and PostgreSQL you can sort distinct salaries descending and skip the first one.

OFFSET 1 skips the highest.
LIMIT 1 keeps just the next one.

DISTINCT is essential here, otherwise duplicate top salaries would make OFFSET 1 land on a repeat of the maximum instead of the genuine runner-up.

Trap: if there is no second distinct value, this returns zero rows, not NULL. We will fix that edge case in lesson 4.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Way 3: FETCH for SQL Server and Oracle

SQL Server and modern Oracle do not support LIMIT ... OFFSET. They use the ANSI standard OFFSET ... FETCH syntax instead.

The logic is identical to Way 2: order distinct salaries descending, skip one row, fetch one. Knowing the cross-dialect spelling signals real-world experience to an interviewer.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

Way 4: DENSE_RANK window function

The modern, scalable approach uses a window function. DENSE_RANK assigns rank 1 to the highest salary, rank 2 to the next distinct salary, and gives tied salaries the same rank with no gaps.

We compute the rank in a subquery, then filter for rank 2 in the outer query. Remember: you cannot filter on a window function directly in WHERE, so the subquery wrapper is mandatory.

SELECT salary AS second_highest
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) ranked
WHERE rnk = 2;

Why DENSE_RANK, not RANK or ROW_NUMBER

The choice of ranking function matters for "distinct" semantics:

ROW_NUMBER gives every row a unique number, so two people earning 300 would be rows 1 and 2 and rank 2 would be a repeat of the top salary. Wrong.
RANK leaves gaps after ties: two 300s get rank 1, then the next salary jumps to rank 3. You would miss it at rank 2. Wrong.
DENSE_RANK gives ties the same rank and no gaps, so rank 2 is always the second distinct salary. Correct.

Way 5: Correlated subquery count

A classic pre-window-function trick: a salary is the Nth highest if exactly N-1 distinct salaries are strictly greater than it.

For the second highest, we want exactly one distinct salary above it. This is elegant but can be slow on large tables because the inner count runs per outer row.

It generalizes nicely to Nth highest by changing the count to N - 1, which is why interviewers like to see it.

SELECT salary AS second_highest
FROM employee e
WHERE 1 = (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employee e2
  WHERE e2.salary > e.salary
);

A worked example end to end

Take salaries: 500, 500, 350, 350, 100.

Way 1: MAX is 500, biggest value below 500 is 350. Answer 350.
Way 4 (DENSE_RANK): 500 -> rank 1, 350 -> rank 2, 100 -> rank 3. Rank 2 is 350.
Way 5: for salary 350, exactly one distinct salary (500) is greater. Match. Answer 350.

All five methods agree: the second highest distinct salary is 350, even with duplicates present.

Which one should you reach for

Interview guidance:

State the question first: "Do you want distinct salaries, and NULL if none exists?" Clarifying earns points.
DENSE_RANK is the strongest default answer; it generalizes to Nth and per-group cleanly.
MAX-below-MAX is the best one-liner and returns NULL for free.
LIMIT/OFFSET is concise but dialect-specific and returns no rows on the edge case.

Mentioning trade-offs out loud is what separates a mid-level answer from a junior one.

Common mistakes to avoid

Watch for these traps interviewers plant:

Using ROW_NUMBER instead of DENSE_RANK and getting the top salary twice.
Forgetting DISTINCT in the LIMIT/OFFSET version when duplicate maximums exist.
Assuming ORDER BY salary DESC LIMIT 1,1 returns a distinct value (it does not).
Returning the second row instead of the second value.

Quick Check

Test your understanding of the ranking-function choice.

Recap

You now have five ways to find the second highest salary:

MAX below MAX - portable, returns NULL for free.
LIMIT/OFFSET and OFFSET/FETCH - concise, dialect-specific.
DENSE_RANK - the scalable default that handles ties correctly.
Correlated count - elegant and generalizes to Nth.

Key takeaways: ask whether you need distinct values, prefer DENSE_RANK for ties, and remember which methods return NULL versus no rows when a second value does not exist.

Frequently asked questions

Is the “Second Highest Salary, Five Ways” lesson free?

Yes — the full text of “Second Highest Salary, Five Ways” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Second Highest Salary, Five Ways”?

Subquery, LIMIT/OFFSET, and window-function solutions compared. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Second Highest Salary, Five Ways” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep