Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 23 Feb 2005 @ 18:05:23 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Finding previous record puzzle
From:   D Rewalt

Anomy Anom wrote:

  How could I select the record previous to a given trans_id, or the previous 5 records; based on the date and time fields?  

Here's what I came up with. It uses a derived table which uses rank to create a sequence number. Hope this helps.

     select txn_id, txn_dt, txn_tm
     from (                          /* Derived table named tbA */
       select trans_id as txn_id,
              trans_date as txn_dt,
              trans_time as txn_tm,
              RANK ( trans_date , trans_time DESC) as seq
       from tableA
       where trans_date || trans_time <
         (select trans_date || trans_time
          from tableA
          where trans_id = '1245')   /* <-- enter trans_id here            */
       and trans_date >              /* Optional - If the tableA is large  */
         (select trans_date - 2      /* then limit derived table size by   */
          from tableA                /* only pulling previous and current  */
          where trans_id = '1245')   /* days' data (in case result set     */
                                     /* spans two days)                    */
       ) as tbA
     where seq < 6                   /* Controls number of rows returned   */
                                     /* In this case 5 rows are returned   */
     order by 2 desc, 3 desc

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023