Forum

Forums
SQL
Interview Questions...
Select the 3rd High...
 
Share:
Notifications
Clear all

Select the 3rd Highest Salary in SQL Using LIMIT

 
    Last Post
RSS

Posts: 81
 SQL Admin
Admin
Topic starter
Jan 10, 2025 5:19 am
(@sql-admin)
Estimable Member
Joined: 4 years ago

SQL is a powerful tool for managing data, and one common requirement is to retrieve specific information, such as the 3rd highest salary from an employee database. This task often appears in technical interviews and real-world scenarios. In this comprehensive guide, we will learn how to achieve this using the LIMIT clause in SQL. Additionally, we'll explore alternative approaches and best practices.

Understanding the Problem

When dealing with a database table, such as an Employees table, identifying the nth highest salary involves sorting and selectively retrieving data. The LIMIT clause is a simple and efficient solution, especially in databases like MySQL.

Let’s assume we have the following table:

Employee_ID Name Salary
1 Alice 70000
2 Bob 85000
3 Charlie 60000
4 Diana 90000
5 Edward 85000

We want to determine the 3rd highest unique salary in this dataset.

Using SQL Query with LIMIT

The SQL LIMIT clause helps in controlling the number of rows returned by a query. Here’s how you can retrieve the 3rd highest salary:

sql
 
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 2, 1;

Step-by-Step Explanation

  1. SELECT DISTINCT Salary:
    This ensures only unique salaries are considered. If multiple employees have the same salary, duplicates are removed.

  2. FROM Employees:
    Specifies the source table for the query.

  3. ORDER BY Salary DESC:
    Sorts the salaries in descending order (highest to lowest).

  4. LIMIT 2, 1:

    • The first number (2) skips the top two salaries.
    • The second number (1) returns one record, which is the 3rd highest salary.

Execution on Example Data

  1. Unique Salaries:
    After applying DISTINCT, the salaries are: 90000, 85000, 70000, 60000.

  2. Sort Salaries in Descending Order:
    90000, 85000, 70000, 60000.

  3. Skip the First Two Salaries:
    Skipping 90000 and 85000, the next salary is 70000.

  4. Result:
    The 3rd highest salary is 70000.

Alternative Methods

1. Using Subqueries

You can use subqueries to filter out the top two salaries and find the 3rd highest:

sql
 
SELECT MAX(Salary) AS ThirdHighestSalary
FROM Employees
WHERE Salary NOT IN (
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 2
);

Explanation:

  • The subquery retrieves the top two distinct salaries.
  • The outer query selects the maximum salary not in the top two.

2. Using Common Table Expressions (CTEs)

If your database supports Common Table Expressions, they provide a more readable solution:

sql
 
WITH RankedSalaries AS (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees
)
SELECT Salary
FROM RankedSalaries
WHERE Rank = 3;

Explanation:

  • The DENSE_RANK() function assigns a unique rank to each salary.
  • The WHERE clause filters for the 3rd rank.

3. Using ROW_NUMBER()

For databases like SQL Server, the ROW_NUMBER() function is an excellent alternative:

sql
 
WITH RankedSalaries AS (
SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees
)
SELECT Salary
FROM RankedSalaries
WHERE RowNum = 3;

This approach assigns a unique row number to each salary in descending order and retrieves the desired row.

Handling Edge Cases

  1. Insufficient Data:
    If there are fewer than three distinct salaries, the query returns no result. To handle this, check for null or empty results in your application.

  2. Duplicate Salaries:
    Using DISTINCT ensures that duplicate salaries do not skew the results.

  3. Performance Considerations:
    Ensure the Salary column is indexed for faster sorting and filtering.

Conclusion

Retrieving the 3rd highest salary using LIMIT is straightforward and efficient for MySQL. In more complex environments, methods like subqueries or window functions can offer additional flexibility and clarity. By understanding these approaches, you can confidently tackle similar problems in interviews or database applications.

For more SQL tips, visit our SQL forum. Explore solutions, share your knowledge, and connect with SQL enthusiasts worldwide.

For additional SQL tutorials, check out this comprehensive SQL guide.

Topic Tags
3rd highest salary SQL SQL query using LIMIT find nth highest salary SQL subquery examples SQL ranking queries

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
Topic Tags:  3rd highest salary SQL (1) , SQL query using LIMIT (1) , find nth highest salary (2) , SQL subquery examples (2) , SQL ranking queries (1) ,
Share: