problem with group by and maximum

Butscheroni · #1 (**permalink**) 11-24-04, 11:05

Hi guys,
i have the following table:

FIELD1 - FIELD2 - FIELD3
1 ------- A ------- 23
1 ------- B ------- 77 <<< i want to select this row
2 ------- C ------- 12
2 ------- D ------- 99 <<< and this one
2 ------- E ------- 17
3 ...

I need to select FIELD1 and FIELD2 where FIELD3 is a maximum of the group grouped by FIELD1!

If i leave out FIELD2 the following query works:
select FIELD1, max(FIELD3)
from TABLE
group by FIELD1

But i dont know how to get FIELD2 selected as well.

Any ideas?

Thanks in advance.
Alex

tuenty · #2 (**permalink**) 11-24-04, 13:21

Code:

SELECT   *
FROM     YourTable A
WHERE    EXISTS
(
     SELECT   Date, Time, max(rev#)
     FROM     YourTable B
     GROUP BY Date, Time
     HAVING   A.Date = B.Date AND A.Time = B.Time AND A.rev# = B.max(rev#))

Internet Services

Web Development

Digital Marketing

Consumer Tech