SQL: Find the Second or Nth Highest Value

Consider the following Employees table. We’ll focus on the Salary column.

+----+-----------+-----------+--------+
| ID | LastName  | FirstName | Salary |
+----+-----------+-----------+--------+
|  1 | Davolio   | Nancy     |   3500 |
|  2 | Fuller    | Andrew    |   2500 |
|  3 | Leverling | Janet     |   2500 |
|  4 | Peacock   | Margaret  |   5500 |
|  5 | Buchanan  | Steven    |   7500 |
|  6 | Suyama    | Michael   |   6000 |
+----+-----------+-----------+--------+

Below are a few queries that can be used to return the second highest value in a result set. Lets begin…

Dense_Rank Window Function

DENSE_RANK() is a window function that assigns a rank to each row within a partition of a result set. You may know of similar functions within this “family” such as: ROW_NUMBER, NTILE and RANK.

• DENSE_RANK returns consecutive rank values within your ordered partition. No ranks are skipped if there are ranks with multiple items.
• In comparison, theRANK function operates similarly but handles duplicates differently: ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK() is preferred function for our use case because of its handling of duplicate values ( we want to assign a consecutive rank across all salaries).

• Whats happening in the inner query? First, the Salary column is sorted into descending order (largest to smallest). Then, a new column called ‘rank’ is created which is populated by the DENSE_RANK() function. It simply goes down the table from top to bottom (largest to smallest) and assigns ranks.
• The outer query simply selects the row that has an assigned rank of 2. Our SELECT clause will return the Salary of the row with rank 2. Thus, we have found our second highest Salary from the Employees table.
• To find the second lowest/smallest value you could change DESC to ASC thereby flipping how the salary column is ordered to be smallest to largest.

MAX() less than

• “Give me the highest salary from the Employees table where the salary is smaller than the maximum salary in the Employees table”

Interested in re-purposing this to calculate the second lowest salary? Swap out the MAX() for MIN() and change ‘less than’ to ‘greater than.’

SELECT  MIN(Salary)
FROM    Employees
WHERE   Salary > ( SELECT MIN(Salary) FROM Employees )

MAX() NOT IN

• “Give me the highest salary from the Employees table where the salary is NOT the maximum salary in the Employees table”

MIN() TOP 2

• Select the TOP 2 highest salaries from the Employees table.
• From this top 2 list, give me the smallest (minimum) salary.

TOP

• The inner select runs first, selecting the top 2 salaries.
• The outer select then picks the bottom salary from the top 2.

• Note how we are defining how the ORDER BY should apply sorting: The inner query is arranging largest to smallest. Then, the outer query we are re-arranging this temp table (containing two records only) into smallest to largest and selecting the top 1 (minimum) value.

LIMIT

The following will return the Nth Maximum element.

• For the 2nd maximum you can replace N = 2, and for 3rd maximum replace N = 3.

Nth-Maximum-Element

• The DISTINCT keyword is there to deal with any duplicate salaries in the table.

Inner query

SELECT COUNT(DISTINCT(Salary))
FROM Employees e2
WHERE e1.Salary <= e2.Salary

• The inner query counts the number of records (salaries) which are greater than the ones returned by e1 for every row. So for each row, it will count the number of rows above it.

WHERE N = (SELECT COUNT(DISTINCT(Salary))

• This says where N = the result of the inner query.
• If N=3, the inner query effectively returns the top 3 salaries, and the WHERE clause is selecting the 3rd one….thereby giving you the 3rd highest salary.