All Forums Database
yarinzim 4 posts Joined 05/13
03 Nov 2014
Using data from previous / next row as condition

Hi guys,
I'm looking for a way to do a sort of a CASE WHEN while using data from other rows.
for example - let's say i have the following dataset:

ID

EVENT

1

A

1

B

1

C

I want to be able to do something like:

SELECT ID, CASE WHEN EVENT = 'A' THEN 1 ELSE 0 END FROM TABLE WHERE EVENT = 'B'

and by EVENT = 'A' i want to be able to "point" to a row above me.
i hope the explenation is clear enough - any help would be much appreciated.
Thansk, Yarin.

yarinzim 4 posts Joined 05/13
08 Nov 2014

Hi,
this is what i'm basically trying to achieve:
http://stackoverflow.com/questions/21120395/case-to-check-if-previous-record-matches-last-record
Thanks, Yarin.

dnoeth 4628 posts Joined 11/04
09 Nov 2014

Hi Yarin,
you need the LAG function, both LAG/LEAD are not implemented in Teradata., but easy to rewrite:

LEAD(data_col, offset, defaultvalue) OVER (ORDER BY order_col) 
= 
COALESCE(MIN(data_col)
         OVER (ORDER BY order_col 
               ROWS BETWEEN offset FOLLOWING AND offset FOLLOWING), defaultvalue)


LAG(data_col, offset, defaultvalue) OVER (ORDER BY order_col) 
=
COALESCE(MIN(data_col)
         OVER (ORDER BY order_col 
               ROWS BETWEEN offset PRECEDING AND offset PRECEDING), defaultvalue)

Both offset and default are optional:

  • offset defaults to 1
  • defaultvalue defaults to NULL, so simply remove the COALESCE

 

Dieter

You must sign in to leave a comment.