Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Wellington, NZ
    Posts
    5

    Unanswered: selecting records using a snapshot time

    Hi,
    Have many tables in a data warehouse that just have a snapshot time to differentiate between each set of records for a particular item. I am trying to work out the best way to select only the latest record for each item (the latest snapshot).

    Here is a simple example table

    TYPE,SNAPSHOT_TIME,DESCRIPTION
    B,14/04/2003 23:01:42,Room
    C,14/04/2003 23:01:42,Club
    B,17/11/2003 23:57:46,Meeting Room
    A,17/11/2003 23:57:46,Temporary
    N,17/11/2003 23:57:46,National Office

    Select would retrieve four records with record type of B having the description of Meeting Room.

    Many Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select TYPE,SNAPSHOT_TIME,DESCRIPTION
    from example as foo
    where SNAPSHOT_TIME =
    ( select max(SNAPSHOT_TIME) from example where TYPE = foo.TYPE )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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