Query Using 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

SQL Server 2000 Forums

Transact-SQL (2000)

Query Using Max

Author

Topic

winsonlee
Starting Member

4 Posts

Posted - 2008-05-26 : 01:08:39

id name class mark
1 John Deo Four 75
2 Max Ruin Three 85
3 Arnold Three 55
4 Krish Star Four 60
5 John Mike Four 60
6 Alex John Four 55

How can I query a result that return the max mark for each class with the name as well ?

Eg
1 John Deo Four 78
3 Max Ruin Three 85

nr
SQLTeam MVY

12543 Posts

Posted - 2008-05-26 : 01:24:35

select *
from tbl t1
where mark = (select max(t2.mark) from tbl t2 here t1.class = t2.class)

or
select t1.*
from tbl t1
join (select class, mark = max(mark) from tbl group by class) t2
on t1.class = t2.class
and t1.mark = t2.mark

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

winsonlee
Starting Member

4 Posts

Posted - 2008-05-26 : 02:37:08

It works. Thanks heaps.

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-05-26 : 08:46:15

SELECT id,name,b.class,b.m FROM @t a
INNER JOIN
(SELECT class,max(mark) m FROM @t GROUP BY class) b
ON a.class=b.class
WHERE a.mark=b.m
ORDER BY id

chandan Joshi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 08:53:47

How do you want to handle ties?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources