How to get previous row values

Database
Enthusiast

How to get previous row values

Hi All,

i need to assign previous row value if current row has null. i have seen posts for this. But since thosed max/min the value is getting changed. 

when i use the sql i am not getting the expected output;

select id,name,case when place is null then coalesce(min(place) over (partition by name),null) Else place end from table;

output of SQL is:

id  name place
1 xxx NY
2 xxx CA
3 yyy NE
4 xxx NJ
5 xxx CA
6 zzz TX
7 xxx CA
8 xxx CA
9 xxx CA

Source:
id name place
1 xxx NY
2 xxx
3 yyy NE
4 xxx NJ
5 xxx
6 zzz TX
7 xxx
8 xxx CA
9 xxx

Need:
id name place
1 xxx NY
2 xxx NY
3 yyy NE
4 xxx NJ
5 xxx NJ
6 zzz TX
7 xxx NJ
8 xxx CA
9 xxx CA

Thanks,

7 REPLIES
Enthusiast

Re: How to get previous row values

Hi,

Sorry, placed wrong SQL, here is the sql

select id,name,case when place is null then coalesce(min(place) over (partition by name rows between 2 preceding and 1 preceding),null) Else place end from table;
Senior Apprentice

Re: How to get previous row values

What's your release?

In TD14.10 there's 

LAST_VALUE(place IGNORE NULLS) 
over (partition by name
ORDER BY id)
Enthusiast

Re: How to get previous row values

Hi Dieter,

Thanks a lot.

yes, i am using TD14.10. It works perfectly.

Fan

Re: How to get previous row values

Hello!

 

For some reason, this solution did not work for us in Teradata

 

LAST_VALUE(place IGNORE NULLS) 
over (partition by name
ORDER BY id)

 

We found a different method that does work, which uses the following solution:

 

min(number_field) over(partition by name order by number_field rows between 1 preceding and 1 preceding) as last_one

 

Does anyone know why one works and the other doesn't?

 

Senior Apprentice

Re: How to get previous row values

What do you mean by did not work

 

Your MIN is the same as Standard SQL's LAG, of course this is not the same as the LAST_VALUE.

Fan

Re: How to get previous row values

Thanks for being willing to help!

 

I think all we need to know is the difference between the lag function and the last_value function. Could you explain the difference bewteen those two functions? Thanks!

Senior Apprentice

Re: How to get previous row values

LAG(column, n) OVER (ORDER BY ...) returns the value of column n rows before the current (n defaults to 1), there's no window definition (no ROWS) option. It's the same as MIN(column) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND n PRECEDING).

LEAD is equivalent to ROWS BETWEEN n FOLLOWING AND n FOLLOWING.

 

LAST_VALUE(column) returns a value from the last row of the defined window. It defauls to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is quite useless, as it's always the current row's value. But adding the IGNORE NULLS option returns the last non-NULL value.