Select the last increment of the value column by id
I'm trying to just subset, but I have a little problem with applying both of my conditions at the same time. I want to select for each unique ID the most recent record where the value column has increased. For example, here is a simplified version of my data.
ID| Date | Value
A 1/1/17 100
A 1/2/17 185
A 1/3/17 40
A 1/4/17 100
A 1/5/17 30
B 1/3/17 40
B 1/4/17 30
B 1/5/17 65
B 1/6/17 80
B 1/7/17 0
B 1/8/17 0
Whereas my desired output would be a string where the last increase in value took place for each id
ID| Date | Value
A 1/4/17 100
A 1/6/17 80
Does anyone have any idea how I can achieve the desired result? Thank.
+3
source to share
2 answers
Two steps:
select t.*
from (select t.*,
row_number() over (partition by id order by date desc) as seqnum
from (select t.*,
max(value) over (partition by id order by date rows between 1 preceding and 1 preceding) as prev_value
from t
) t
where prev_value < value
) t
where seqnum = 1;
In Teradata, you can also use qualify
to remove one level of subqueries.
0
source to share
You can try this.
select id,dt,value
from (select t1.*
,max(case when t2.value<t1.value then t1.dt end) over(partition by t1.id) as max_dt
from (select t.*,min(dt) over(partition by id order by dt rows between 1 preceding and 1 preceding) as prev_dt
from t
) t1
join t t2 on t1.prev_dt=t2.dt and t1.id=t2.id
) t
where max_dt=dt
0
source to share