All Forums Database
NewbieTD 25 posts Joined 05/13
15 Oct 2015
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,

NewbieTD 25 posts Joined 05/13
15 Oct 2015

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;

 

dnoeth 4628 posts Joined 11/04
15 Oct 2015

What's your release?
In TD14.10 there's 

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

 

Dieter

NewbieTD 25 posts Joined 05/13
16 Oct 2015

Hi Dieter,
Thanks a lot.
yes, i am using TD14.10. It works perfectly.

You must sign in to leave a comment.