Get the previous row's column Value

Database
Vga
Enthusiast

Get the previous row's column Value

Hello All,

I have got source data in following way:

Col_1 | Col_2

100 | 2000

100 | 3000

100 | 4000

300 | 5000

300 | 6000

300 | 7000

I need to generate an extra column as Col_3 (based upon Col_2) where it contains the value from previos row, starting from 0 for  every group based on Col_1. The outcome expected as:

Col_1 | Col_2 | Col_3

100 | 2000 | 0

100 | 3000 | 2000

100 | 4000 | 3000

300 | 5000 | 0

300 | 6000 | 5000

300 | 7000 | 6000

Help !!!

11 REPLIES
Junior Contributor

Re: Get the previous row's column Value

select
coalesce(min(col_2) over (partition by col_1 order by col_2 rows between 1 preceding and 1 preceding), 0)

Dieter

Enthusiast

Re: Get the previous row's column Value

Awesome! dieter

Re: Get the previous row's column Value

Can somebody tell for next rows syntax? SUCCEEDING ??

Enthusiast

Re: Get the previous row's column Value

I thibk this is what you are looking for.

rows between 1 following and 1 following 

Re: Get the previous row's column Value

Dears,

Can some one help please .I arrive at a wrong result in CALL_SEQUENCE at the step highlighted in yellow

EVT_DT |CLE|DELTA|CALL_SEQUENCE|CORRECT_CALL_SEQUENCE
19/11/2013 12:37| 10222014| -1| 1| 1
22/11/2013 19:00| 10222014| 3| 2| 2
22/11/2013 20:01| 10222014| 0| 3| 3
25/11/2013 18:51| 10222014| 3| 4| 4
05/12/2013 17:47| 10222014| 10| 1| 1
05/12/2013 18:37| 10222014| 0| 6| 2
07/12/2013 17:44| 10222014| 2| 7| 3
16/07/2014 20:18| 10222014| 221| 1| 1
16/07/2014 20:25| 10222014| 0| 9| 2


Formula to calculate call_sequence:

ORDER BY CLE,EVT_DT
WHEN DELTA=-1 OR DELTA >3 THEN 1 ELSE ADD 1 TO PRECEDING CALL_SEQUENCE



My code :There's an issue because 1 is being added to the row_number when I actually want it to be added to previos call_sequence 

SEL EVT_DT 
,CLE
,DELTA
,CASE WHEN DELTA=-1 OR DELTA>3 THEN 1 ELSE
ROW_NUMBER() OVER ( PARTITION BY CLE ORDER BY CLE,EVT_DT ASC ) END AS CALL_SEQUENCE
FROM (
SEL EVT_DT
,CLE
,CASE WHEN CLE=MAX(CLE) OVER (ORDER BY CLE,EVT_DT ASC ROWS BETWEEN
1 PRECEDING AND 1 PRECEDING)
THEN CAST(EVT_DT AS DATE )-MAX(CAST(EVT_DT AS DATE )) OVER ( ORDER BY CLE,EVT_DT ASC ROWS
BETWEEN 1 PRECEDING AND 1 PRECEDING) ELSE -1 END AS DELTA
FROM TABLE_NM ADV
WHERE CLE IN ('10222013','10222014','10222015') ORDER BY CLE,EVT_DT


Fan

Re: Get the previous row's column Value

Hi All , 

I need help for the below scenario.

SOURCE DATA >>>> (S1,S2,S3...S300)

 

 TABLE DATA

 

 SID, UID 

 S1, U1

 S2 , U5

 S3,  U1

 S1, U7

 S3,U7

 S7,U1

 .....

  NOW OUTPUT OF THIS DATA SHOULD BE LIKE 

 

CONDITION IF SOURCE DATA COMING ONLY FOR SID (S1 ,S3)

IT SHOULD DISPLAY THE UID HAVING BOTH OF THESE VALUES .

EXAMPLE THE OUTPUT SHOULD COME AS 

UID >>> U1  AND U7

 

SO THE SITUATION IS LIKE , IT MAY HAPPEN THAT SOURCE DATA CAN COME AS ONE 'SID' VALUE  

OR MORE THAN ONE 'SID' VALUES .

IT IS DYNAMIC

THE SOURCE DATA WILL COME ALWAYS (S1,S2,....) FORMAT ONLY WE HAVE TO SEPERATE EACH SID VALUE .

 

SO , HOW CAN WE ACHIVE THIS USING ONLY  'TERADATA SQL'

Junior Contributor

Re: Get the previous row's column Value

This will return the UID if both values exist:

select uid
from tab
where SID in ('S1','S2') -- only searched values
group by uid
having min(SID) <> max(SID)-- both must exist

If you need to search for more than two values change to:

having count(distinct SID) = 2 -- number of searched values

If there are no duplicate values you can remove the DISTINCT.

But what if there's an additional value like 'S3', the previous will still return the UID.

If you need exactly 'S1' and 'S2' but no other value:

select uid
from tab
group by uid
having
count(distinct case when SID in ('S1','S2') then SID end) = 2 -- both values
and
count(case when SID in ('S1','S2') then NULL else SID end) = 0 -- but no other value

Again, if there are no duplicate values you can remove the DISTINCT

Fan

Re: Get the previous row's column Value

Thanks Dieter for you Instant Reply,

But in my case  the values or the count is not fixed . It may happen that we can get two values or Twelve values .

In such case where we dont know how many Source ID (SID) will come , how can we handle it ?

Fan

Re: Get the previous row's column Value

WITH YOU Query Dieter ! when we use the IN operator it will also give those user names(UID) which are having only permission like (S1).

SID is permission like Grant , Read , Execute , Write so on  S1......S300

UId are the usernames U1....Un

so one UID can have many Permissions or vice versa.

Now suppose our Client needs  to know how many users  are having the access of Read(s7) and Grant(s12) 

So it should display the userid , having both the Permissions.But this permission values is not fixed , means any number of Permissions can come as Input ranging (S1 to S300) so we cannot write a fixed values query to acheive it .

APart from this , the value will be like (S1,S8,S9)  then we need to seperate each of the Permission and find the required User id .

I hope m clear enough this time :P .