Tuesday 15 October 2013

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 SERVERHow 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.

No comments:

Post a Comment

© 2012-2018 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.