SQL SERVER – Find Nth Highest Record from Database Table

The fourth Highest Record depends on whether ties occupy separate places. I define the ranking rule before choosing a query.

A reader asked me to adapt the earlier nth-maximum query to the sample database. The correlated COUNT DISTINCT method answers that request while preserving every pay-history row at the chosen rate.

-- Run in the intended AdventureWorks sample database.
DECLARE @N int = 4;
IF @N < 1 THROW 50000, 'N must be positive.', 1;
SELECT e1.BusinessEntityID, e1.RateChangeDate, e1.Rate, e1.PayFrequency
FROM HumanResources.EmployeePayHistory AS e1
WHERE e1.Rate IS NOT NULL
  AND @N - 1 = (SELECT COUNT(DISTINCT e2.Rate)
                FROM HumanResources.EmployeePayHistory AS e2
                WHERE e2.Rate > e1.Rate)
ORDER BY e1.BusinessEntityID, e1.RateChangeDate;

Each qualifying rate has exactly three greater distinct rates when N is four. Tied pay-history rows all qualify. An N beyond the distinct known rates returns no row.

The sample screenshot belongs to its database version and data. Don’t present its employee or rate as the guaranteed output of another AdventureWorks release. The table is pay history, so several rows can belong to one employee.

The explicit ORDER BY controls presentation, not the ranking rule. If the question instead asks for a single fourth sorted row, ROW_NUMBER with an agreed tie-breaker answers a different question.

Related reading

• SQL SERVER – Query to Retrieve the Nth Maximum value

A highest-value question is not complete without a tie rule, it is a ranking problem.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.