Mastering SQL: Finding Nth Highest and Nth Minimum Salaries

Finding Nth Highest and Nth Minimum Salary in SQL

Here are SQL queries to find the 2nd Highest Salary, 2nd Minimum Salary, Nth Highest Salary, and Nth Minimum Salary from an employees table.

In SQL, the OFFSET clause is used in conjunction with the LIMIT clause to control which rows are returned in a query result. Specifically, OFFSET specifies the number of rows to skip before starting to return rows from the query.

<number> after LIMIT indicates how many rows to return.

<number> after OFFSET indicates how many rows to skip.

1. 2nd Highest Salary

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

Explanation: This query sorts salaries from highest to lowest and skips the highest salary to return the second-highest.

2. 2nd Minimum Salary

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

Explanation: This query sorts salaries from lowest to highest and skips the minimum salary to return the second minimum.

3. Nth Highest Salary

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

Explanation: Replace N with the desired position (e.g., 3 for the third highest salary).

4. Nth Minimum Salary

SELECT DISTINCT salary
FROM employees
ORDER BY salary ASC
LIMIT 1 OFFSET N-1;

Explanation: Replace N with the desired position to find the Nth minimum salary.

Using DENSE_RANK() Window Function (Alternative)

2nd Highest Salary using DENSE_RANK()

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

Nth Highest Salary using DENSE_RANK()

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM employees
) ranked_salaries
WHERE rank = N;

Performance Considerations

Consider these points for better performance:

• Indexes: Having an index on the salary column can speed up queries.
• Data Size: For large datasets, the query structure impacts performance.
• Using DISTINCT: Omit if you know there are no duplicates.
• Window Functions: Efficient for complex operations but can require sorting.

Always monitor and optimize based on your data characteristics for the best performance.