Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: max col in a table subset

    hi i have a table as follows:

    col1 col2 col3
    2 BB 2
    1 AB 3
    3 CA 3
    1 AC 1
    2 BA 4
    3 CB 2
    2 BC 6
    1 AA 2
    3 CC 5

    i want to output the whole row of each unique col1 where its col3 is max, so that i will get the ff result set
    1 AB 3
    2 BC 6
    3 CC 5

    i have an idea which is to use a cursor but i don't know the best/fastest way of doing it.
    thanks heaps in advance!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select col1
         , col2
         , col3
      from daTable as foo
     where col3 =
         ( select max(col3)
             from daTable
            where col1 = foo.col1 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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