11 different ways to select Second / Nth highest value in MS SQL Server Instance

Let’s discuss 11 different ways to select second highest value in MS SQL table. And as a bonus, 6 different ways to select the Nth highest value.

I will be using a table called WorkOrder with three columns in it; WorkOrderID, ProductID, OrderQty.

Here is a quick look to our table:

WorkOrderID   ProductID   OrderQty
1                          995                10
2                          995                5
3                          996                3 
4                          996                6
5                          997                20
6                          998               18
7                          998                3
8                          998                8
9                          998                12
10                        999                5

Let’s start:

  1. 2nd highest value in SQL using Max () function
SELECT MAX (ProductID)
  FROM Production.WorkOrder
 WHERE ProductID < (SELECT MAX (ProductID) FROM Production.WorkOrder);
  1. 2nd highest value in SQL using Max() function
SELECT MAX (ProductID)
  FROM Production.WorkOrder
 WHERE ProductID NOT IN (SELECT MAX (ProductID) FROM Production.WorkOrder);
  1. nth highest value in SQL using inline query with TOP function
DECLARE @N INT;
SET @N=2;
SELECT TOP 1 ProductID
  FROM (SELECT DISTINCT TOP ( @N ) ProductID
        --The distinct keyword is used to remove duplicates
          FROM Production.WorkOrder
        ORDER BY ProductID DESC) MAXTwo
ORDER BY ProductID ASC;
--for the 2nd maximum you need to set @N = 2, and for 3rd maximum need to set @N = 3
--or you can replace @N with 2, or replace @N with 3, and remove declare and set statement
  1. nth highest value in SQL using inline query
DECLARE @N INT;
SET @N=2;
SELECT DISTINCT ProductID
--The distinct keyword is used to remove duplicates
  FROM Production.WorkOrder a
 WHERE @N = (SELECT COUNT (DISTINCT (b.ProductID))
--for the 2nd maximum you need to set @N = 2, and for 3rd maximum need to set @N = 3
--or you can replace @N with 2, or replace @N with 3, and remove declare and set statement
              FROM Production.WorkOrder b
             WHERE a.ProductID <= b.ProductID);
--Cons: Slow, because the inner query will run for every row processed by the outer query.
  1. 2nd highest value in SQL using LEAD OVER function
SELECT TOP 1 LEAD (MAX (ProductID)) OVER (ORDER BY ProductID DESC)
  FROM Production.WorkOrder
GROUP BY ProductID;
  1. nth highest value in SQL using OFFSET / FETCH function
DECLARE @N INT;
SET @N=2;
SELECT DISTINCT ProductID
--The distinct keyword is used to remove duplicates
  FROM Production.WorkOrder
ORDER BY ProductID DESC
OFFSET @N ROW
FETCH NEXT 1 ROW ONLY;
--for the 2nd maximum you need to set @N = 2, and for 3rd maximum need to set @N = 3
--or you can replace @N with 2, or replace @N with 3, and remove declare and set statement
  1. nth highest value in SQL using MIN() function
DECLARE @N INT;
SET @N=2;
SELECT MIN (ProductID)
  FROM Production.WorkOrder
 WHERE ProductID IN (SELECT DISTINCT TOP ( @N ) (ProductID)
                     --The distinct keyword is used to remove duplicates
                       FROM Production.WorkOrder
                     ORDER BY ProductID DESC);
--for the 2nd maximum you need to set @N = 2, and for 3rd maximum need to set @N = 3
--or you can replace @N with 2, or replace @N with 3, and remove declare and set statement
  1. nth highest value in SQL using ROW_NUMBER () OVER PARTITION function
DECLARE @N INT;
SET @N=2;
SELECT ProductID
  FROM (SELECT ROW_NUMBER () OVER (PARTITION BY NULL ORDER BY ProductID DESC) AS RN,
               ProductID
          FROM Production.WorkOrder
        GROUP BY ProductID) d
 WHERE RN = @N;
--for the 2nd maximum you need to set @N = 2, and for 3rd maximum need to set @N = 3
--or you can replace @N with 2, or replace @N with 3, and remove declare and set statement                    
  1. nth highest value in SQL using HAVING() function
DECLARE @N INT;
SET @N=2;
SELECT MIN (ProductID)
  FROM (SELECT DISTINCT TOP (@N) ProductID
        --The distinct keyword is used to remove duplicates
          FROM Production.WorkOrder
        ORDER BY ProductID DESC) T
HAVING MIN (ProductID) <> MAX (ProductID);
--for the 2nd maximum you need to set @N = 2, and for 3rd maximum need to set @N = 3
--or you can replace @N with 2, or replace @N with 3, and remove declare and set statement
  1. 2nd highest value in SQL using LAG() OVER() function
SELECT TOP 1 LAG (ProductID, 1, 0) OVER (ORDER BY ProductID)
  FROM Production.WorkOrder
ORDER BY ProductID DESC;                       
  1. nth highest salary in SQL using DENSE_RANK () OVER () function
DECLARE @N INT;
SET @N=2;
WITH TMP_WorkOrder
     AS (SELECT ProductID, DENSE_RANK () OVER (ORDER BY ProductID DESC) AS R
           FROM Production.WorkOrder)
SELECT DISTINCT ProductID
--The distinct keyword is used to remove duplicates
  FROM TMP_WorkOrder
 WHERE TMP_WorkOrder.R = @N;
--for the 2nd maximum you need to set @N = 2, and for 3rd maximum need to set @N = 3
--or you can replace @N with 2, or replace @N with 3, and remove declare and set statement

                    

And here is the result of all  the queries:

Second - Nth highest value

Please contact us  if you would like us to help you with your SQL Server Database challenges.

Scroll to top