Home > DeveloperSection > Forums > How to find the Nth maximum and minimum value in sqlserver.
Ankit Singh

Total Post:341

Points:2389
Posted on    January-13-2016 10:20 PM

 Database SQL Server  SQL  SQL Server 2008  SQL Server 2012 
Ratings:


 1 Reply(s)
 293  View(s)
Rate this:
I want to To find the Nth maximum and minimum value in sqlserver.


aditya kumar Patel

Total Post:250

Points:1782
Posted on    January-13-2016 10:23 PM

DECLARE @table TABLE(ID integer,AMOUNT integer)

INSERT INTO @table values(1, 5000)

INSERT INTO @table values(2, 200)

INSERT INTO @table values(3, 4500)

INSERT INTO @table values(4, 3000)

INSERT INTO @table values(5, 3200)

INSERT INTO @table values(6, 3100)

INSERT INTO @table values(7, 150)

SELECT ID, AMOUNT

FROM

(

SELECT ID, AMOUNT, Row_Number() OVER(ORDER BY AMOUNT DESC) AS highest

FROM @table

) as x

WHERE highest = 1

SELECT id, AMOUNT

FROM

(

SELECT id, amount, Row_Number() OVER(ORDER BY AMOUNT ASC) AS lowest

FROM @table

 

) as x

WHERE lowest =1

 


Don't want to miss updates? Please click the below button!

Follow MindStick