# 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, the
`RANK`

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.

- 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.