Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: problem with group by and maximum

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    108

    use the Exists clause or maybe a join

    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#))
    to err is human ; to really mess things up requires a computer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •