Group By Function with Max - 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

General SQL Server Forums

New to SQL Server Programming

Group By Function with Max

Author

Topic

LeighA
Starting Member

1 Post

Posted - 2008-11-06 : 14:53:15

Hi. I need to group some student data by student id with each student's max test score. Here is the data:

Student A1 10/31/08 100
Student A1 11/06/08 99
Student Z2 10/31/08 75
Student Z2 11/06/08 88

I want to know for each student the max test score and what date that was achieved. Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 15:00:14

select student, date, score from (
select student, date, score, row_number() over (partition by student order by score desc) AS recid from table1
) AS f
where recid = 1

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 23:38:32

If sql 2000 or earlier

SELECT t1.*
FROM table1 t1
INNER JOIN (SELECT Student,MAX(Score) AS MaxScore
            FROM table1
            GROUP BY Student)t2
ON t2.,Student=t1.Student
AND t2.MaxScore=t1.Score

andrewcw
Posting Yak Master

133 Posts

Posted - 2015-03-19 : 00:44:56

That's exactly what I needed and simple to follow ! Thanks !!!

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources