Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 05 Sep 2003 @ 18:53:41 GMT


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


Subj:   Re: LEAD/LAG Analysis
 
From:   Watzke, Michael

Vinod:

If you mean lead/lag as defined in Oracle then you can use a different Teradata syntax to achieve the same capability.

This is best shown by example.

drop table t;
ct t (c1 integer, c2 integer);
insert into t values (1,10);
insert into t values (2,11);
insert into t values (3,14);

SELECT c1,c2,MIN(c2) OVER (ORDER BY c2 ROWS BETWEEN 1 FOLLOWING AND 1
FOLLOWING) as LEAD
FROM t;

 *** Query completed. 3 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

         c1           c2         LEAD
-----------  -----------  -----------
          1           10           11
          2           11           14
          3           14            ?

+---------+---------+---------+---------+---------+---------+---------+----

SELECT c1,c2,MIN(c2) OVER (ORDER BY c2 ROWS BETWEEN 1 PRECEDING AND 1
PRECEDING) AS LAG
FROM t;

 *** Query completed. 3 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

         c1           c2          LAG
-----------  -----------  -----------
          1           10            ?
          2           11           10
          3           14           11

Oracle syntax

SELECT c1,c2,LEAD(c2, 1) OVER (ORDER BY c2) AS "LEAD" FROM t



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