Forum

Forums
SQL
Interview Questions...
How to Find the Sec...
 
Share:
Notifications
Clear all

How to Find the Second Highest Salary in SQL - Step-by-Step Guide

 
    Last Post
RSS

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

Finding the second highest salary in SQL is a common interview question that tests your knowledge of SQL queries and logical problem-solving. In this article, we will explore multiple approaches to solve this problem with examples and explanations.

Why Is This Question Important?

Many database systems require fetching the Nth highest or lowest salary for reporting and analysis. It tests your understanding of SQL clauses, sorting, and filtering methods. Knowing how to approach such queries demonstrates your ability to handle real-world data challenges efficiently. Whether you're preparing for an interview or working on a business report, this query is a must-have in your SQL toolkit.

Method 1: Using LIMIT with OFFSET (MySQL and PostgreSQL)

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

Explanation:

  • DISTINCT ensures unique salaries are considered.
  • ORDER BY salary DESC sorts salaries in descending order.
  • LIMIT 1 OFFSET 1 skips the first salary and fetches the second highest.

Use Case:
This method is simple and works well for databases that support LIMIT and OFFSET. However, it may not work in SQL Server, which lacks these clauses.

Method 2: Using Subquery

sql
 
SELECT MAX(salary) AS SecondHighestSalary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Explanation:

  • First, fetch the highest salary using MAX().
  • Then filter salaries less than the maximum and apply MAX() again to get the second highest salary.

Use Case:
This approach works in most databases and is useful when dealing with small datasets. However, it can be slower for larger datasets since it requires nested queries.

Method 3: Using RANK() Window Function

sql
 
SELECT salary
FROM (
SELECT salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees
) temp
WHERE rank = 2;

Explanation:

  • The RANK() function ranks salaries in descending order.
  • We filter out only the row where rank equals 2.

Use Case:
This method is efficient and works well with large datasets. It leverages window functions, which are optimized for analytics.

Method 4: Using DENSE_RANK() Window Function

sql
 
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees
) temp
WHERE rank = 2;

Difference between RANK() and DENSE_RANK():

  • RANK() skips ranks if duplicates exist.
  • DENSE_RANK() does not skip ranks, making it useful for datasets with duplicate salaries.

Use Case:
When duplicates are expected, use DENSE_RANK() to avoid missing the second-highest salary.

Method 5: Using ROW_NUMBER() Window Function

sql
 
SELECT salary
FROM (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees
) temp
WHERE row_num = 2;

Explanation:

  • ROW_NUMBER() assigns a unique rank to each row.[
  • We fetch the row where the rank equals 2.

Use Case:
This method is suitable for datasets without duplicates and ensures accurate results.

Best Practices

  • Always test queries on sample data before applying them in production.
  • Optimize performance by adding indexes on salary columns.
  • Use window functions for better readability and scalability.
  • For large datasets, ensure indexes are created to improve query performance.
  • Benchmark queries with different approaches to find the most efficient one for your database.

Common Errors and Troubleshooting

  • Handling NULL values: Ensure NULL salaries are excluded or handled appropriately.
  • Performance issues: Use indexes to speed up queries on large datasets.
  • Compatibility: Check database-specific syntax before implementing queries.
  • Duplicates: Use functions like DENSE_RANK() to handle duplicate values properly.
  • Syntax errors: Double-check query syntax, especially in databases like SQL Server, MySQL, and Oracle, where implementations can vary slightly.

Conclusion

In this guide, we explored multiple ways to find the second highest salary in SQL. Each approach has its own benefits and use cases, depending on database compatibility and requirements. Mastering these methods will help you excel in SQL interviews and database-related tasks. Whether you're working with small datasets or large-scale applications, these techniques can be adapted to meet your needs.

By learning these techniques, you’ll be able to handle salary-based queries confidently and optimize performance with well-structured SQL code.

Topic Tags
how to find the second hi SQL query for second hig second highest salary in SQL interview questions find nth highest salary

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
Topic Tags:  how to find the second hi (1) , SQL query for second hig (1) , second highest salary in (1) , SQL interview questions (2) , find nth highest salary (2) ,
Share: