Forum

Forums
SQL
Interview Questions...
How to Select the 3...
 
Share:
Notifications
Clear all

How to Select the 3rd Highest Salary in SQL Using LIMIT

 
    Last Post
RSS

Posts: 97
 SQL Admin
Admin
Topic starter
Feb 16, 2025 2:35 pm
(@sql-admin)
Estimable Member
Joined: 5 years ago

How to Select the 3rd Highest Salary in SQL Using LIMIT

Retrieving specific data, such as the 3rd highest salary from an employee database, is a common task in SQL. This guide demonstrates how to achieve this using the LIMIT clause in SQL, along with alternative methods for various database systems.

Using the LIMIT Clause in SQL

In SQL databases like MySQL and PostgreSQL, the LIMIT clause restricts the number of rows returned by a query. To find the 3rd highest salary:

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

This query sorts unique salaries in descending order and skips the first two, returning the third highest salary.

Alternative Methods

1. Using Subqueries

For databases that do not support the LIMIT clause, a subquery can be used:

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

This approach finds the maximum salary less than the top two maximum salaries, effectively retrieving the third highest salary.

2. Using Common Table Expressions (CTEs) and Window Functions

In SQL Server or PostgreSQL, CTEs combined with window functions offer a clear solution:

WITH RankedSalaries AS (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT salary
FROM RankedSalaries
WHERE rank = 3;

This method assigns ranks to each salary and selects the one with a rank of 3.

Considerations

  • Handling Ties: Use DENSE_RANK() to assign the same rank to identical salaries.
  • Performance: Ensure the salary column is indexed to optimize query performance.
  • Database Compatibility: Syntax may vary between SQL dialects; adjust queries accordingly.

By applying these methods, you can effectively retrieve the 3rd highest salary in SQL, a valuable skill for database management and technical interviews.

Topic Tags
SQL 3rd highest salary SQL LIMIT clause SQL interview question SQL salary query SQL ranking functions

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
Topic Tags:  SQL 3rd highest salary (1) , SQL LIMIT clause (1) , SQL interview question (1) , SQL salary query (1) , SQL ranking functions (3) ,
Share: