1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

Second Highest Salary in SQL: 5 Tested Queries and the Tie Trap

Finding the second highest salary in SQL is the most famous interview query, and most answers break on ties. We ran five methods on the same table, including duplicate top salaries, NULLs and a table with one salary, and show which ones return the right answer.

9 min read
Second highest salary in SQL: five tested queries and the tie trap

Key takeaways

  • When two people share the top salary, the second highest salary is the next distinct value. In our table that is 9000, not 12000.
  • ORDER BY salary DESC LIMIT 1 OFFSET 1 without DISTINCT returned 12000, the wrong answer.
  • DENSE_RANK() = 2 returned the correct salary and both employees who earn it. RANK() = 2 returned no rows at all.
  • Wrap the query as a scalar subquery to get NULL instead of an empty result when there is no second salary.

“Write a query to find the second highest salary” appears in SQL interviews for developers, testers and data analysts alike. It looks trivial, and that is the point: the interviewer wants to see whether you think about duplicate values, missing data and empty results. A query that works on a neat sample table can quietly return the wrong number on real data.

We ran every query below on SQLite 3.50.4. The ideas carry over to every database, and we list the syntax changes for MySQL, PostgreSQL, SQL Server and Oracle near the end. For more practice questions, see our SQL interview questions for testers.

What is actually being asked

Before writing SQL, settle two questions, ideally out loud in an interview:

  • Distinct value or second row? If two people earn the top salary, is the answer the next lower amount, or that same top amount again? Almost always, the intended answer is the second highest distinct salary.
  • Salary only, or the people too? Returning just the number is simpler; returning every employee who earns it needs a different query.

The test table, with a tie on purpose

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INT);
INSERT INTO employees VALUES
 (1,'Asha','Eng',9000), (2,'Ben','Eng',12000), (3,'Chen','QA',12000),
 (4,'Dina','QA',7000),  (5,'Eli','Eng',9000),  (6,'Fay','Ops',6000);

Ben and Chen share the top salary of 12000. Asha and Eli share 9000. So the distinct salaries in order are 12000, 9000, 7000, 6000, and the correct second highest salary is 9000.

Method 1: MAX with a subquery

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
second_highest
9000

The inner query finds 12000. The outer query takes the maximum of everything strictly below it. Because it compares values, not rows, ties at the top cannot fool it. It also works on any SQL database, even very old ones without window functions, which makes it the safest default answer. Its weakness is that it only generalises to the third or fourth highest by nesting more subqueries, which gets ugly fast. Our SQL subqueries guide explains how the nesting is evaluated.

Method 2: ORDER BY with LIMIT and OFFSET

The popular short answer sorts salaries and skips the first one:

SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
salary
12000

Wrong. The first row is Ben at 12000 and the second row is Chen, also at 12000. The query skipped a row, not a value. Adding DISTINCT fixes it:

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
salary
9000

With DISTINCT, this is short, readable and easy to change to the Nth highest by changing the offset to N minus 1. It is a good answer as long as you say why the DISTINCT is there.

Tip: in an interview, run your query in your head against a table with a tie at the top. It is the test case the interviewer is waiting for, and it is the same habit as boundary value analysis in testing.

Method 3: DENSE_RANK

Window functions give the most flexible answer. DENSE_RANK gives equal salaries the same rank and leaves no gaps, so rank 2 is always the second distinct salary:

SELECT name, salary FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
) AS ranked
WHERE dr = 2;
name | salary
Asha | 9000
Eli  | 9000

This returns the people as well as the amount, and both employees who earn 9000 appear. Add DISTINCT and select only salary if you just want the number. It needs window function support: MySQL 8.0 or later, SQLite 3.25 or later, and any current PostgreSQL, SQL Server or Oracle.

Why RANK and ROW_NUMBER give the wrong answer

We swapped in the other two numbering functions and kept everything else the same:

-- RANK() ... WHERE r = 2
name | salary
(no rows)

-- ROW_NUMBER() ... WHERE rn = 2
name | salary
Chen | 12000

RANK gave both 12000 earners rank 1 and then skipped to rank 3, so no row has rank 2. ROW_NUMBER gave the tied 12000 earners numbers 1 and 2, so it returned a top earner. Both are classic interview mistakes. Our guides to RANK vs DENSE_RANK and SQL ROW_NUMBER show the full numbering side by side.

Method 4: correlated subquery

A pre-window-function trick counts how many distinct salaries are higher than each one:

SELECT DISTINCT salary FROM employees e1
WHERE 1 = (SELECT COUNT(DISTINCT salary) FROM employees e2
           WHERE e2.salary > e1.salary);
salary
9000

A salary is the second highest when exactly one distinct salary is above it. Replace 1 with N - 1 for the Nth highest. It is correct and portable, but the inner query runs once per row, so it can be slow on large tables. It is mainly worth knowing because interviewers sometimes ask for a solution without LIMIT or window functions.

Method 5: second highest salary per department

The common follow-up question. Add PARTITION BY so ranking restarts in each department:

SELECT dept, name, salary FROM (
  SELECT dept, name, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
  FROM employees
) AS ranked
WHERE dr = 2;
dept | name | salary
Eng  | Asha | 9000
Eng  | Eli  | 9000
QA   | Dina | 7000

Ops has only one employee, so it has no second highest salary and does not appear. Say that out loud: if the business wants Ops listed with an empty value, you would need a LEFT JOIN from a department list.

Generalising to the Nth highest salary

Any of the scalable methods change with one number. For the third highest:

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;
salary
7000

With DENSE_RANK, use WHERE dr = 3; with the correlated subquery, use WHERE 2 = (...). The nested MAX approach is the only one that does not generalise cleanly.

Edge cases we tested

Only one distinct salary

We made a table where both employees earn 5000:

-- MAX subquery
second_highest
NULL

-- LIMIT 1 OFFSET 1 without DISTINCT
salary
5000

-- scalar subquery with DISTINCT
SELECT (SELECT DISTINCT salary FROM solo ORDER BY salary DESC LIMIT 1 OFFSET 1) AS second_highest;
second_highest
NULL

The MAX method returns NULL because MAX of no rows is NULL. The plain LIMIT query wrongly returns 5000. A bare DISTINCT ... LIMIT ... OFFSET query returns an empty result, which some applications and test harnesses handle badly; wrapping it in an outer SELECT ( ... ) turns the empty result into a single NULL row. LeetCode’s version of this problem expects exactly that NULL.

NULL salaries

We added an employee with a NULL salary. The descending DISTINCT query still returned 9000, because SQLite sorts NULL first in ascending order and last in descending order. PostgreSQL and Oracle do the opposite and sort NULL first in descending order, so there NULL could be returned as the highest value. Add WHERE salary IS NOT NULL to be safe on every database. MAX and DENSE_RANK ignore or isolate NULL differently, which our SQL NULL values guide covers in detail.

Syntax for other databases

Database Skip one distinct salary
MySQL, PostgreSQL, SQLite SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET 1
SQL Server 2012+, Oracle 12c+, PostgreSQL ... ORDER BY salary DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
SQL Server (older) SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 2 salary FROM employees ORDER BY salary DESC) AS t ORDER BY salary ASC
All of them The MAX subquery and DENSE_RANK versions work unchanged

We only ran the SQLite versions; the other rows are standard syntax from each database’s documentation and were not executed here.

Which method should you use?

Method Handles ties Nth highest Returns names Portable
MAX subquery Yes Awkward No Everywhere
LIMIT OFFSET Only with DISTINCT Easy No Syntax varies
DENSE_RANK Yes Easy Yes Needs window functions
Correlated subquery Yes Easy Can Everywhere, slower

In real code, use DENSE_RANK: it handles ties, scales to N, works per group and returns full rows. In an interview, give the MAX subquery first as the safe answer, then show DENSE_RANK and explain the tie behaviour.

How to answer in an interview

  1. Ask whether ties should count once. State your assumption if there is no answer.
  2. Write the MAX subquery, because it is correct and short.
  3. Offer DENSE_RANK for the Nth highest or per-department versions.
  4. Mention the edge cases: one salary, NULL values and empty results.
  5. Mention an index on salary for large tables.

Final thoughts

The second highest salary in SQL is a small query that tests big habits: define the requirement, think about ties, and check empty and NULL cases. In our tests the MAX subquery, DISTINCT with LIMIT OFFSET, DENSE_RANK and the correlated subquery all returned the right answer, while plain LIMIT OFFSET, RANK and ROW_NUMBER failed on the tie. Remember which is which and you will handle every version of this question.

Frequently asked questions

What is the simplest query for the second highest salary in SQL?

SELECT MAX(salary) FROM employees WHERE salary is less than the result of SELECT MAX(salary) FROM employees. It handles ties and works on every SQL database.

Why does LIMIT 1 OFFSET 1 sometimes return the highest salary?

If two employees share the top salary, the second row still holds the top value. Add DISTINCT so the query skips a value rather than a row.

Should I use RANK or DENSE_RANK for the second highest salary?

Use DENSE_RANK. RANK skips numbers after a tie, so with two top earners no row gets rank 2 and the query returns nothing.

How do I find the Nth highest salary?

Use DENSE_RANK and filter on rank N, or use SELECT DISTINCT salary ordered descending with LIMIT 1 OFFSET N minus 1.

What should the query return if there is no second highest salary?

Usually NULL. The MAX subquery returns NULL naturally, and wrapping a LIMIT query in an outer SELECT turns an empty result into a single NULL row.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Levenshtein Distance: Formula, Worked Table and Python Code

Levenshtein distance explained: the edit operations, the dynamic programming table for kitten and sitting, Python code with two-row memory, normalised…

Read article →
Software Development

URL Encoding Explained: Spaces, Plus Signs and UTF-8

URL encoding explained with tested Python and JavaScript code: percent encoding, reserved characters, %20 vs plus, UTF-8 bytes, encodeURI vs…

Read article →
DevOps

Gitignore: Patterns, Negation and Fixing Tracked Files

A tested gitignore guide: pattern rules, leading and trailing slashes, negation with !, the ignored parent folder trap, git check-ignore…

Read article →
Software Testing

Acceptance Criteria: Given When Then Examples and Checklist

How to write acceptance criteria testers can run: rule-based and Given When Then formats, a worked discount code example turned…

Read article →
Software Testing

State Transition Testing: Diagram, Table and Example

State transition testing explained with a login lockout example: states, events, the diagram, the state table, valid and invalid transitions,…

Read article →
Databases & SQL

Second Highest Salary in SQL: 5 Tested Ways and the Tie Trap

Find the second highest salary in SQL five ways: MAX subquery, LIMIT OFFSET, DENSE_RANK, a correlated subquery and per department,…

Read article →
Databases & SQL

SQL ROW_NUMBER: Top N per Group, Dedupe and Pagination

SQL ROW_NUMBER explained with tested queries: PARTITION BY, top N per group, deleting duplicates, pagination, the tie trap and how…

Read article →
Software Development

JavaScript Set: Union, Intersection and Removing Duplicates

JavaScript Set explained with tested examples: add, has, delete, deduplicating arrays, the new union and intersection methods, equality rules and…

Read article →
Test Automation

Python Unittest: Assert Methods, setUp and Mocks With Examples

A practical Python unittest tutorial: TestCase, assert methods, setUp and tearDown, subTest, skips, expected failures and mock, with a test…

Read article →
Software Development

Python Map and Filter: Examples vs List Comprehensions

Python map and filter explained with tested examples: lazy map objects, multiple iterables, filter(None), chaining, and a timed comparison with…

Read article →
Artificial Intelligence

Jaccard Similarity: Formula, Python, MinHash

Jaccard similarity measures overlap between two sets as intersection over union. Work the formula, compute it in Python for words…

Read article →
Software Development

Unix Timestamp Explained: Seconds vs ms

A Unix timestamp counts seconds since 1 January 1970 UTC. Learn seconds vs milliseconds, converting in Python, JavaScript and SQL,…

Read article →
DevOps

Git Hooks: Pre-commit and Commit-msg Examples

Git hooks run scripts at key moments such as commit and push. See tested pre-commit, commit-msg and prepare-commit-msg hooks, sharing…

Read article →
Test Automation

API Testing in Python: Pytest + Requests Suite

Build an API testing suite in Python with pytest and requests: fixtures, status codes, headers, JSON schema validation, CRUD flows,…

Read article →
Software Testing

Test Design Techniques: 9 Methods + Examples

Test design techniques help you write fewer tests that catch more bugs. Learn 9 black-box, white-box and experience-based methods with…

Read article →
Databases & SQL

SQL Temporary Table: Create, Use and Drop It

SQL temporary tables store interim results for one session. Learn syntax for SQL Server, PostgreSQL, MySQL, Oracle and SQLite, session…

Read article →
Databases & SQL

SQL CROSS JOIN: Every Combination + Examples

SQL CROSS JOIN returns every combination of rows from two tables. See the row math, product variants, calendar grids that…

Read article →
Software Development

JavaScript Date: Format, Parse, Timezone Bugs

JavaScript Date explained with tested Node.js code: zero-based months, the date-only UTC parsing trap, toISOString, getTimezoneOffset, Intl formatting, date math…

Read article →
Software Development

Python Requests: GET, POST, Timeouts, Retries

Python requests explained with code tested against a local API: GET with params, POST json vs data, headers and auth,…

Read article →
Artificial Intelligence

TF-IDF Explained: Formula and Worked Example

TF-IDF scores a word by how often it appears in a document and how rare it is across all documents….

Read article →
DevOps

Git Tag: Create, Push and Delete Release Tags

Git tag explained with tested commands: annotated vs lightweight tags, listing and sorting, pushing with follow-tags, deleting local and remote…

Read article →
Test Automation

Pytest Coverage: pytest-cov and Branch Gaps

Measure pytest coverage with pytest-cov: term-missing reports, branch coverage, HTML reports, pyproject settings and a fail-under gate for CI, with…

Read article →
Software Testing

Error Guessing in Testing: Checklist + Demo

Error guessing uses tester experience to predict where code breaks. Get a ready checklist of risky inputs, a step by…

Read article →
Databases & SQL

SQL LAG and LEAD: Compare Rows, 5 Examples

SQL LAG and LEAD read values from the previous or next row without a self join. Five tested examples: change,…

Read article →
Databases & SQL

SQL STRING_AGG: Combine Rows Into One String

SQL STRING_AGG joins values from many rows into one string per group. See syntax for PostgreSQL, SQL Server, MySQL GROUP_CONCAT,…

Read article →
Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →