Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    35

    Unanswered: Don't understand GROUP BY...

    I have a table Test_GUID, with fields id as int PK IDENTITY, GUID as varchar(50), and Version as tinyint. I put data in it such as
    Code:
    id   GUID    Version
    ------------------
    1    abc     1
    2    abc     2
    3    def     1
    Then I tried to get the following result:
    Code:
    id    GUID    Version
    -------------------
    1     abc     2
    3     def     1
    By using a query:
    Code:
    SELECT     GUID, MAX(Version) AS MaxVersion, id
    FROM         dbo.Test_GUID
    GROUP BY GUID
    But then I get an error because id is not being aggregated.

    This works
    Code:
    SELECT     GUID, MAX(Version) AS MaxVersion
    FROM         dbo.Test_GUID
    GROUP BY GUID
    but then how do I get the id of each record?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a correlated subquery, it's the same as grouping
    Code:
    select id
         , GUID
         , Version as MaxVersion
      from dbo.Test_GUID as foo
     where Version
         = ( select max(Version)
               from dbo.Test_GUID
              where GUID
                  = foo.GUID )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    35
    Thanks, was able to incorporate this into other queries, works great!

Posting Permissions

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