Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Posts
    55

    Question Unanswered: Finding most recent

    Hi,

    I have a table that has an ID field, status and a date (it keeps an audit of
    users changing a status field in another table).

    In the table, their can be multiple instances of the same status and the same ID, but the date is a timestamp and will always be unique to the three field record.

    What I need to do is figure out a way to extract the most recent date only.

    For example if I had the following data:
    ID Status Timestamp
    11 NEW 1/1/2005 11:03 PM
    11 CLOSED 1/3/2005 11:11 PM
    09 NEW 1/1/2005 11:02 PM

    I would expect to see only rows 2 and 3 in my output.

    Any thoughts on such a query would be appreciated!

    TIA

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    seems simple (probly means i don't understand the question!)

    SELECT TOP 1 blah FROM tblBlah WHERE whatever ORDER BY timestamp DESC

    ...you promised tstamp will be unique so you wont get the normal bull$#1t issues with A's stupid WITH TIES un-undoable default

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry -- i didn't even read the question correctly

    can you afford another field
    iAmCurrentRecord, y/n ??

    or two tables: tblOldStuff & tblCurrentStuff ??

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i think you misunderstood, i think hirkos wants the latest per ID
    Code:
    select ID
         , Status
         , Timestamp
      from yourtable as t1
     where Timestamp
         = ( select max(Timestamp)
               from yourtable
              where ID = t1.ID )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2002
    Posts
    55

    That did the trick...

    You were correct R937. That worked perfectly. Thank you both for the quick response time!

Posting Permissions

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