2nd highest in a group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

2nd highest in a group

Author

Topic

roshana
Starting Member

31 Posts

Posted - 2009-10-26 : 02:25:26

Hi All
I have to find out 2nd highest in a group.
Subject Student Mark
Physics ABC 48
Physics BCD 47
Physics XYZ 43
Maths ABC 40
Maths BCD 49
Maths XYZ 45
Chemistry ABC 47
Chemistry BCD 44
Chemistry XYZ 49

Here i have to find out students who got second rank in different subject
Exp Ans
Physics BCD 47
Maths XYZ 45
Chemistry ABC 47

Thanks
Roshan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-26 : 02:27:53

[code]
select *
from
(
select *, rank = rank() over (partition by Subject order by Mark desc)
from yourtable
) r
where rank = 2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

roshana
Starting Member

31 Posts

Posted - 2009-10-26 : 02:31:59

Thanks Khtan
It is working fine

Thanks
Roshan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-26 : 04:20:40

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx

Madhivanan

Failing to plan is Planning to fail

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-19 : 08:28:20

You can try this.. This is query is for finding nth maximum...

SELECT t1.mark
FROM table AS t1
WHERE n = (SELECT COUNT(*)
FROM table AS t2
WHERE t1.mark >= t2.mark
GROUP BY subject)

Here,
table -> your table name
n -> position(if u want 2nd position you mentioned it as 2)

Balaji.K

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources