Archives of the TeradataForum

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

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Anomy Anom wrote:

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	Next>	Last>>