The query to find the Nth Maximum and Minimum values in an SQL Server table column, using the Row_Number() function. We will find the 3rd highest and the 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 = 3
-- 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