Find the Nth Maximum and Minimum Value in a Column

Find the Nth Maximum and Minimum Value in a Column: "from Sql Server Blog by noreply@blogger.com (Suprotim Agarwal)

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 3rd 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 = 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

OUTPUT


The first output shows the 3rd maximum value in the column whereas the second output shows the 3rd minimum value in the column