Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Question Unanswered: How to select the last record per unique ID

    I am using MS SQL 2005.

    I would like to select the last row per unique ID in my table.

    Sample data:

    ID__YEAR___ENTRY_______EXIT_______LOC__SUB-LOC
    1___2011___2011-03-03__2011-03-29__509__503
    1___2011___2011-03-29__2011-04-15__509__502
    2___2011___2010-08-23__2011-01-16__511__501
    2___2011___2011-01-16__0001-01-01__511__511
    3___2011___2011-03-03__2011-03-29__510__510
    4___2011___0001-01-01__2010-08-23__020__020

    I would like the results to look like:

    ID__YEAR___ENTRY_______EXIT_______LOC__SUB-LOC
    1___2011___2011-03-29__2011-04-15__509__502
    2___2011___2011-01-16__0001-01-01__511__511
    3___2011___2011-03-03__2011-03-29__510__510
    4___2011___0001-01-01__2010-08-23__020__020

    Can anyone tell me how to accomplish this?
    Last edited by BridgetN; 09-08-11 at 13:15.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.id 
         , t.year  
         , t.entry  
         , t.exit  
         , t.loc  
         , t.[sub-loc]
      FROM ( SELECT id
                  , MAX(entry) AS latest
               FROM sampletable
             GROUP
                 BY id ) AS m
    INNER
      JOIN sampletable AS t
        ON t.id = m.id
       AND t.entry = m.latest
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Posts
    2
    Thank you!!!! Works like a charm.

Posting Permissions

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