Please read the Flexibility using TOP clause in SQL Server 2005 for more details.
Lets say, we are having student and their marks, and we want the nth max mark. First will see the table structure and will add few data into it.
SET NOCOUNT ON
DECLARE @Marks TABLE
(
StudName VARCHAR(100),
Mark INT
)
INSERT INTO @Marks VALUES('AAAAA', 55)
INSERT INTO @Marks VALUES('BBBBB', 65)
INSERT INTO @Marks VALUES('CCCCC', 59)
INSERT INTO @Marks VALUES('DDDDD', 52)
INSERT INTO @Marks VALUES('FFFFF', 65)
INSERT INTO @Marks VALUES('EEEEE', 95)
SELECT Mark FROM @Marks ORDER BY Mark DESC
Mark
-----------
95
65
65
59
55
52
Now we write the query which allow us to find nth max mark form this list.
DECLARE @Top INT
SET @Top = 2
SELECT MIN(Mark) AS 'Top' FROM(
SELECT DISTINCT TOP (@Top) Mark FROM @Marks ORDER BY Mark DESC) A
Top
-----------
65
@Top is variable; which gives you the ability to fetch Nth max.
2 comments:
Hey guys,
Above query works well, though I have couple of comments.
1. How to get all the students who got second highest marks (there are 2 students who scored 65 in above example)?
2. What if someone is working with previous version of sql server or does not have the liberty to declare a variable?
Ok Ok, dont scratch ur heads, soluton is right here :)
Try this: -
SELECT * FROM @Marks A Where
1 = (SELECT COUNT(DISTINCT Mark) FROM @Marks B WHERE B.Mark > A.Mark)
Happy Querying...
-Viral
Hi All,
This problem can be solved efficiently, using the latest feature of SQL Server, which is common table expression and Rank() function.
The sql looks as follows:
with cte_marks (name, marks, Rnk) as
(select name,marks, Rank() over(order by marks desc) as Rnk
from @Marks)
select * from cte_marks where Rnk =3
This solution is more efficient, since we can query any rank by simply changing the value in the where clause.
Happy computing.... :)
Post a Comment