2 Replies Latest reply: Sep 13, 2011 9:07 AM by 846465 RSS

    sql query to show latest records only

    846465
      Hi All,

      We have a table for eg test which have a timestamp column and stores historical data.
      For eg

      id change_type changed_attr timestamp
      1 change intserv 3/29/2005 2:41:02 PM
      2 change intserv 3/29/2005 2:41:02 PM
      3 change intserv 3/29/2005 2:41:02 PM
      4 change intserv 3/29/2005 2:41:02 PM
      5 change intserv 3/27/2005 3:41:02 PM
      6 change intserv 3/27/2005 3:41:02 PM
      7 change intserv 3/27/2005 3:41:02 PM
      8 change intserv 3/27/2005 3:41:02 PM
      9 change intserv 3/25/2005 2:41:02 PM
      10 change intserv 3/25/2005 2:41:02 PM
      11 change intserv 3/25/2005 2:41:02 PM
      12 change intserv 2/24/2005 2:41:02 PM
      13 change intserv 2/24/2005 2:41:02 PM
      and so on

      How can I have the latest and the second latest records ONLY and ignore all the older records?
      Very much appreciate your help.

      1 change intserv 3/29/2005 2:41:02 PM
      2 change intserv 3/29/2005 2:41:02 PM
      3 change intserv 3/29/2005 2:41:02 PM
      4 change intserv 3/29/2005 2:41:02 PM
      5 change intserv 3/27/2005 3:41:02 PM
      6 change intserv 3/27/2005 3:41:02 PM
      7 change intserv 3/27/2005 3:41:02 PM
      8 change intserv 3/27/2005 3:41:02 PM

      Thanks a lot for your help.
      Dk
        • 1. Top-N Query
          Frank Kulash
          Hi,

          It looks like you're really interested in the two latest values of tmstmp, not the two latest records (or "rows" as they are called in relational databases).
          That's called a Top-N Query, and here's one way to do it:
          WITH     got_r_num     AS
          (
               SELECT     id, change_type, changed_attr, tmstmp     -- TIMESTAMP is not a good column name
               ,     DENSE_RANK () OVER (ORDER BY tmstmp  DESC  NULLS LAST)   AS r_num
               FROM     table_x
          )
          SELECT     id, change_type, changed_attr, tmstmp
          FROM     got_r_num
          WHERE     r_num     <= 2
          ;
          If you'd care to post CREATE TABLE and INSERT statemnts for the sample data, then I could test it.

          After you look up the DENSE_RANK fucntion, look up its close relatives RANK and ROW_NUMBER; they're all useful for similar problems.
          • 2. Re: Top-N Query
            846465
            Thanks a lot Frank.
            It is very useful tip. I will test it.

            Thanks
            Dk