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
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...
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.
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