How To Get Nᵗʰ Highest Value Using Plain Sql

What is the simplest way to get the nth highest value from a result set using plain SQL? The result set would be huge, thus need to consider performance too.

Solution 1:

This is the T-SQL (SQL-Server 2005 and greater) approach using ROW_NUMBER:

WITH CTE AS
(
   SELECT 
      Col1, Col2, ValueCol,
      RN =ROW_NUMBER() OVER (ORDERBY ValueCol DESC) -- change to ASC if you want lowest firstFROM 
      dbo.TableName
)
SELECT 
   Col1, Col2, ValueCol
FROM 
   CTE
WHERE 
   RN =@nthhighestvalue

If you want all rows with the same value use DENSE RANK instead.

Difference between ROW_NUMBER, RANK and DENSE_RANK

Solution 2:

This article talks about this question in depth, and I will quote code from it below:

Solution 1: This SQL to find the Nth highest salary should work in SQL Server, MySQL, DB2, Oracle, Teradata, and almost any other RDBMS: (note: low performance because of subquery)

SELECT*/*This is the outer query part */FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */SELECTCOUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

The most important thing to understand in the query above is that the subquery is evaluated each and every time a row is processed by the outer query. In other words, the inner query can not be processed independently of the outer query since the inner query uses the Emp1 value as well.

In order to find the Nth highest salary, we just find the salary that has exactly N-1 salaries greater than itself.

---

Solution 2: Find the nth highest salary using the TOP keyword in SQL Server

SELECT TOP 1 Salary
FROM (
      SELECTDISTINCT TOP N Salary
      FROM Employee
      ORDERBY Salary DESC
      ) AS Emp
ORDERBY Salary

---

Solution 3: Find the nth highest salary in SQL Server without using TOP

SELECT Salary FROM Employee 
ORDERBY Salary DESCOFFSET N-1ROW(S) 
FETCHFIRSTROWONLY

Note that I haven’t personally tested the SQL above, and I believe that it will only work in SQL Server 2012 and up.

---

Solution 4: Works in MySQL

SELECT Salary FROM Employee 
ORDERBY Salary DESC LIMIT n-1,1

The LIMIT clause takes two arguments in that query – the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.

---

Solution 5: Works in Oracle

select*from (
  select Emp.*, 
row_number() over (orderby Salary DESC) rownumb 
from Employee Emp
)
where rownumb = n;  /*n is nth highest salary*/

---

Solution 6: Works in Oracle way 2

select*FROM (
select EmployeeID, Salary
,rank() over (orderby Salary DESC) ranking
from Employee
)
WHERE ranking = N;

Solution 3:

Start by producing an ordered, numbered dataset and then select from that. The precise syntax depends on the RDBMS but, for example, in Oracle you can do

SELECT ROWNUM, SOMEVAL
FROM (SELECT SOMEVAL FROM SOMETABLE ORDERBY SOMEVAL DESC)

Given the above set you can

SELECT SOMEVAL WHERE ROWNUM = :N

Solution 4:

In a general database, you can do this with a subquery and two order bys. The problem is that top/limit/rownum syntax is not standard. In some databases you would write:

Solution 5:

In Oracle:

SELECT*FROM (
  SELECT col1, ROW_NUMBER()OVER(ORDERBY col1) rnum_col1 FROM table1
) WHERE rnum_col1 =10;