Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Posts
    168

    Unanswered: Detecting last version

    I have this table :

    ID Year Version Score
    1 2002 1 20
    1 2002 2 30
    2 2003 1 40
    2 2003 2 50
    etc


    What I wanna do is populate a listbox with ID and year but only the last (highest version).

    For example:

    ID Year Version Score
    1 2002 2 30
    2 2003 2 40

    So basically, within each ID and year, the last version is the only record I am interested in.


    Any suggestion ?

    Thanks

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    Select ID, MyYear, Max(Version) as MaxOfVersion
    From MyTable
    Group By ID, MyYear;

  3. #3
    Join Date
    Apr 2002
    Posts
    168
    Thanks

    How do I incorporate the score(s) because I need that information for the record ?

    Is using group by the only solution ?

  4. #4
    Join Date
    Nov 2001
    Posts
    336
    Hi,

    Assuming that ID, MyYear and MaxOfVersion uniquely identify a record in your table, you may Join Query1 and MyTable to obtain Score.

    Query1:
    Select ID, MyYear, Max(Version) as MaxOfVersion
    From MyTable
    Group By ID, MyYear;

    HTH, Igor

Posting Permissions

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