Sql query for Top N rows using a group by

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 2008 Forums

Transact-SQL (2008)

Sql query for Top N rows using a group by

Author

Topic

empyrean
Starting Member

14 Posts

Posted - 2011-02-23 : 12:08:50

Hi i tried many ways to solve this but missing some thing.. I have two tables Student and Score

Sid Cid Score
6 1 90
1 1 80
4 1 80
3 1 70
2 1 60
6 2 80
1 2 70
2 2 60
4 2 60
5 2 50
4 3 80
7 3 80
6 3 70
8 3 60
2 3 50

Sid Sname Sbday Ssex
1 As 1980 female
2 Al 1986 male
3 An 1989 male
4 ja 1986 male
5 ma 1983 female
6 phi 1986 male
7 Geo 1993 male
8 lil 1990 female
9 cha 1993 male
I need to Return Sid and Sname of the students who have the top 2 highest score for each course. If existed, return Sid and Sname of the student who has the highest score among all the male students for each course.

Here top 2 highest score is not just top two records in a group for ex : top 2 highest score in 1st group is 90, 80 ,80 .

I need out put like this

Sid Cid Score
6 1 90
1 1 80
4 1 80
6 2 80
1 2 70
2 2 60
4 2 60
4 3 80
7 3 80
6 3 70
I tried the following code :

select A.Sid , S.SNAME, Score,Cid
from Score a,STUDENTS S
where 2 >(select count(Cid)
from Score
where Cid=a.Cid
and Score>a.Score)
AND A.SID = S.SID
order by Cid,Score desc

empyrean
Starting Member

14 Posts

Posted - 2011-02-23 : 12:54:17

got the answer

SELECT SC.Sid , ST.SNAME, SC.Score, SC.Cid
FROM ( SELECT *, DENSE_RANK() OVER(PARTITION BY Cid ORDER BY Score DESC) TopScore
FROM Score) AS SC
INNER JOIN Students AS ST
ON SC.Sid = ST.Sid
WHERE SC.TopScore <= 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources