Forum

Forums
SQL
Interview Questions...
How to Get the Seco...
 
Share:
Notifications
Clear all

How to Get the Second Highest Salary in SQL Using Different Methods

 
Interview Questions & Answers
Last Post by SQL Admin 8 months ago
1 Posts
1 Users
0 Reactions
3,542 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Jul 24, 2025 2:37 am
(@sql-admin)
Reputable Member
Joined: 6 years ago

How to Get the Second Highest Salary in SQL Using Different Methods

Finding the second highest salary from an employee table is a popular SQL interview question and a real-world task used in HR reports and analytics. There are multiple ways to do it, and understanding each method helps improve query-building skills.

Let’s consider a table called employees:

CREATE TABLE employees (
   emp_id INT,
   name VARCHAR(50),
   salary INT
);

Method 1: Using Subquery with MAX()

This is the most readable method and works on almost all databases:

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

  • The inner query fetches the highest salary.

  • The outer query selects the maximum salary less than that, i.e., the second highest.

Method 2: Using DISTINCT and LIMIT (MySQL or PostgreSQL)

This method is helpful when you want nth highest values:

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

  • LIMIT 1 OFFSET 1 skips the top record and returns the second.

  • Works in MySQL, PostgreSQL, and SQLite.

Method 3: Using DENSE_RANK() (SQL Server, Oracle, PostgreSQL)

This is ideal when there are duplicate salary values and you want true rank-based results.

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

  • DENSE_RANK() gives the same rank for duplicates.

  • Returns all employees who share the second-highest salary.

Method 4: Using Subquery with NOT IN

Simple logic but less efficient on large data:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary NOT IN (
   SELECT MAX(salary) FROM employees
);

Use Cases for Second Highest Salary

  • Shortlisting top-performing employees

  • Bonus calculation for second-tier performers

  • HR dashboards showing top-2 or top-5 paid roles

  • Academic score ranking scenarios

Performance Considerations

  • Use indexed salary columns for faster results.

  • Avoid NOT IN for large datasets; prefer DENSE_RANK() or LIMIT OFFSET.

  • Always test queries on real data before using in production.

Summary Table

MethodBest ForDatabase Support
MAX() with <Simple casesAll SQL engines
LIMIT + OFFSETFast for small dataMySQL, PostgreSQL, SQLite
DENSE_RANK()Handling duplicatesOracle, SQL Server, PostgreSQL
NOT INEasy to write but less efficientAll, but avoid on large tables

 


Topic Tags
second highest salary in sql query for 2nd highes sql rank salary query dense_rank sql example mysql second highest sal

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
Topic Tags:  second highest salary in (2) , sql query for 2nd highes (1) , sql rank salary query (1) , dense_rank sql example (1) , mysql second highest sal (1) ,
Share: