Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    19

    Unanswered: Query Help and finding the highest version number

    Hello,
    I think I've been starring at this too long :-p

    what I would like to do is, I would like to return all of the rows with the highest version number for the group_id.

    Quick Table
    app_id, name, group_id, app_ver
    1000, Foo, com.foo, 1.0
    1001, Foo, com.foo, 1.1
    1020, Foo, com.foo, 1.4
    1005, Bar, com.bar, 1.1
    1006, Bar, com.bar, 1.2

    Expected Results
    1020, Foo, com.foo, 1.4
    1006, Bar, com.bar, 1.2

    Thanks,
    tom

  2. #2
    Join Date
    Jan 2010
    Posts
    18
    Something like this.

    select q.app_id, q.name, q.group_id, q.app_ver
    from quick q
    join (select group_id, max(app_ver) as max_app_ver
    from quick
    group by group_id) a on a.group_id = q.group_id
    and a.max_app_ver = q.app_ver

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Or:

    Code:
    select q.app_id, q.name, q.group_id, q.app_ver
         from quick q
    where q.app_ver =(select max(app_ver) from quick q2
                              where q2.unique_cols = q.unique_cols)
    Dave

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dave, i've seen multiple times (no links, though, sorry) where someone has done a benchmark of the "join to max derived table" (rayqsl's version) versus the "where max correlated subquery" (your version) and the derived table always outperforms
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2003
    Posts
    19

    Thumbs up

    Thanks, the join makes sense this help a bunch.

    thanks again,
    tom

Posting Permissions

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