Using data from previous / next row as condition

Database

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.

2 REPLIES

Re: Using data from previous / next row as condition

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.

Junior Contributor

Re: Using data from previous / next row as condition

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