What ate the dogs?

[Yannick Turgeon]

Hi guys!

I want a query that would return me the rows that have the maximum of a
specific field group by another field. Ex:

SELECT *
FROM Dogs
WHERE Speed IS MAX
GROUP BY Dog
(This SELECT query is just to make you understand!)

Number(PK) Dog Food Speed
---------- ----- ---- -----
1 Rex C 21
2 Fido B 42
3 Fido A 27
4 Rex A 33
5 Fido C 12
6 Rex B 25

Should return

Number(PK) Dog Food Speed
---------- ----- ---- -----
2 Fido B 42
4 Rex A 33

I can't figure out how to do that.

Tanks

Yannick

[Larry Calame]

It took me 2 cups of coffee to figure it out, but the following works:

SELECT D1.* FROM DOGS D1,(SELECT Dog, Max(Speed)AS Speed FROM DOGS D2 GROUP
BY Dog) D2
WHERE D1.Dog = D2.Dog AND D1.Speed = D2.Speed

Larry Calame
lca...@home.com

Yannick Turgeon <yanni...@SPAMiname.com> wrote in message
news:398D67DD...@SPAMiname.com...

[Dan Guzman]

Try:

SELECT Dogs.*
FROM Dogs
JOIN (SELECT Dog, MAX(Speed) Speed FROM Dogs GROUP BY Dog) MaxSpeed ON
MaxSpeed.Dog = Dogs.Dog AND
MaxSpeed.Speed = Dogs.Speed

Hope this helps.

[Trysql]

Because I like your title.

select *
from ##dogs as a
where not exists(select 'RAC' from ##dogs as b
where b.dog=a.dog and b.speed>a.speed)
order by 1 desc

Steve Dassin
Check out RAC (replacement for Access crosstab query)
exclusively for ms server 7+.Has many computational
and report options and can do string pivoting/concatenation.
Enterprise enabled.Written entirely in transact-sql
(no cursors!).It will save you from silly query language
syndrome and hate mail from your network administrator.
Take a tour at:
www.angelfire.com/ny4/rac/
questions: try...@aol.com