SQL SERVER: How to find nth maximum value and nth minimum values in a column

Hi friends, in this article I will explain about  SQL SERVER: How to find nth maximum value and nth minimum values in a column.
I already explained in the previous articles about  How to Get Highest and Lowest Values in a Row using SQL SERVER, How to Display DateTime Formats in Different Languages using SQL Server and How to Calculate Age from Date Of Birth using SQL Server

DECLARE @MARKS TABLE(ID INT, MATHS INT,SCIENCE INT) INSERT INTO @MARKS values(1, 95,98) INSERT INTO @MARKS values(2, 34,25) INSERT INTO @MARKS values(3, 54,65) INSERT INTO @MARKS values(4, 89,85) INSERT INTO @MARKS values(5, 76,45) INSERT INTO @MARKS values(6, 56,85) INSERT INTO @MARKS values(7, 98,23) INSERT INTO @MARKS values(8, 100,85) -- FIND MAXIMUM MARKS IN MATHS AND GET THAT PERSON SCIENCE MARKS SELECT id, MATHS,SCIENCE FROM ( SELECT id, MATHS, Row_Number() OVER(ORDER BY MATHS DESC) AS highest_MATHS,SCIENCE,Row_Number() OVER(ORDER BY SCIENCE DESC) AS highest_SCIENCE FROM @MARKS ) as x WHERE highest_MATHS = 1 -- FIND Minimum MARKS IN MATHS SELECT id, MATHS FROM ( SELECT id, MATHS, Row_Number() OVER(ORDER BY MATHS ASC) AS lowest FROM @MARKS ) as x WHERE lowest = 1 --FIND 2ND MINIMUM MARKS IN MATHS SELECT id, MATHS FROM ( SELECT id, MATHS, Row_Number() OVER(ORDER BY MATHS ASC) AS lowest FROM @MARKS ) as x WHERE lowest = 2

The output of the above code as shown in the below figure.