Here’s a simple query to find the Nth Maximum and Minimum values in a SQL Server table column using the Row_Number() function. We will find the 2nd highest and 3rd lowest values in the column.
DECLARE @tmp TABLE(id integer, amount integer)
INSERT INTO @tmp values(4, 9543)
INSERT INTO @tmp values(6, 34)
INSERT INTO @tmp values(3, 54)
INSERT INTO @tmp values(2, 6632)
INSERT INTO @tmp values(5, 645)
INSERT INTO @tmp values(1, 1115)
INSERT INTO @tmp values(7, 345)
-- FIND Nth Maximum value
SELECT id, amount
FROM(SELECT id, amount, Row_Number() OVER(ORDER BY amount DESC) AS highest FROM @tmp) as x
WHERE highest = 2
-- FIND Nth Minimum value
SELECT id, amount
FROM(SELECT id, amount, Row_Number() OVER(ORDER BY amount ASC) AS lowest FROM @tmp) as x
WHERE lowest = 3
