Archives of the TeradataForum

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

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

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