CodeRang
Common problems

Second highest salary SQL: DENSE_RANK and other methods

Second highest salary SQL problems depend on whether equal salaries count as one position or separate rows. Compare DENSE_RANK, ROW_NUMBER, and MAX, then extend the same reasoning to an nth highest salary SQL query.

Updated · SQL Arena team

Interview preparation trackAll problems

What you will practice

  • Find the nth highest distinct value
  • Handle ties with DENSE_RANK
  • Choose between DENSE_RANK and ROW_NUMBER
  • Build top N per group queries
  • Spot edge cases in ranking questions

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Find the second-highest salary in each department

Each employee belongs to a department. Return every employee whose salary is the second-highest distinct salary in that department. If several employees share that salary, keep all of them. Return department, employee, and salary.

Query 1PostgreSQLv16
WITH ranked AS (
  SELECT d.name AS department, e.name AS employee, e.salary,
         DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank
  FROM employees e JOIN departments d ON d.id = e.department_id
)
SELECT department, employee, salary
FROM ranked WHERE salary_rank = 2
ORDER BY department, employee;
Submit
Resultdepartmentsemployees
Answer accepted

How to find the second highest salary in SQL

PARTITION BY e.department_id creates a separate ranking inside every department. Within each partition, salaries are ordered from highest to lowest. DENSE_RANK assigns rank 1 to the highest distinct salary and rank 2 to the next distinct salary. In Sales, 120000 ranks first, both 110000 rows rank second, and 90000 ranks third.

The tie behavior is the reason for using DENSE_RANK. ROW_NUMBER would give Bob and Carol different row numbers even though their salaries are equal. That is correct when the question asks for the second row after sorting, but wrong when it asks for the second-highest salary value. RANK keeps ties too, but it leaves gaps after them; DENSE_RANK maps more directly to consecutive distinct values.

The ranked CTE gives the window-function result a name that can be filtered in the outer query. Filtering salary_rank = 2 then returns every employee at the second distinct salary level. ORDER BY department, employee makes the output deterministic, which matters both for testing and for understanding exactly which rows the query should produce.

For a second highest salary SQL query, distinguish the second row from the second distinct salary value. DENSE_RANK() ordered by salary descending gives equal salaries the same rank, so rank 2 represents the second distinct value. ROW_NUMBER() numbers every row independently and can therefore assign different positions to tied salaries. If only the second distinct salary value is needed, nested MAX expressions are another option. For an nth highest salary SQL query, ranking generalizes naturally because the requested N can be used directly as the rank filter.

SQLPostgreSQL v16
WITH ranked AS (
  SELECT d.name AS department, e.name AS employee, e.salary,
         DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank
  FROM employees e JOIN departments d ON d.id = e.department_id
)
SELECT department, employee, salary
FROM ranked WHERE salary_rank = 2
ORDER BY department, employee;
Query result
departmentemployeesalary
AnalyticsFrank125000
SalesBob110000
SalesCarol110000
SupportIvan85000
SupportJulia85000

Common second highest salary SQL mistakes

01

Using ROW_NUMBER for distinct values

ROW_NUMBER ranks rows, not unique salaries. Two employees on the same salary still receive different numbers. If ties are supposed to represent one position, this silently changes the meaning of the result.

02

Using OFFSET before removing duplicates

ORDER BY salary DESC LIMIT 1 OFFSET 1 returns the second row. If the maximum salary appears twice, the second row can still contain the maximum salary. DISTINCT must come before the offset when the requirement is the nth distinct value.

03

Forgetting PARTITION BY

Without a partition, the ranking is calculated across the entire employee table. You would get the company's second-highest salary rather than the second-highest salary within every department.

Practice problems on this topic

Interview preparation track

A useful next step is to keep the ranking idea while changing the business wording. The free Second-Best Pilot by Passengers task tests whether you can recognize a second-place requirement outside the salary example. Then Second-highest salary focuses directly on the classic interview pattern, while Three highest salaries in each department expands it into top N per group SQL. Harder tasks introduce ordering by events rather than a single numeric column.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

How do I find the second highest salary in SQL?

For the second distinct salary, rank salaries in descending order with DENSE_RANK() and select rank 2. Another option is to find the maximum salary below the overall maximum.

How do I find the second highest salary in SQL with duplicates?

Use DENSE_RANK if equal salary values should share the same position. All tied maximum salaries receive rank 1, and the next distinct salary receives rank 2.

Should I use DENSE_RANK or ROW_NUMBER for second highest salary SQL?

Use DENSE_RANK when ranking distinct salary values and ties should share a position. Use ROW_NUMBER when you specifically need to rank individual rows.

How do I write an nth highest salary SQL query?

Rank salaries in descending order with DENSE_RANK() and filter for the required rank N. This pattern works for the second, third, or any other distinct salary position.

Can I find the second highest salary in SQL without a window function?

Yes. One approach finds the maximum salary among values lower than the overall maximum. This works well when only the second distinct value is required.

See also

Practice second and nth highest salary SQL queries

SQL Arena includes ranking, nth-value, and top-N problems checked against hidden data rather than a fixed output template. For interview-focused practice, continue with the Interview Preparation track. The free plan includes 292 problems across PostgreSQL, MySQL, ClickHouse, and BigQuery without a card.

Practice in SQL Arena

More practice from CodeRang