SQL – How to get only current and previous value for each ID

I have a table

id, date, value
a,  5/22/2019, 22
b,  5/22/2019, 22
c,  5/22/2019, 22
a,  5/21/2019, 21
b,  5/21/2019, 21
c,  5/21/2019, 21
a,  5/20/2019, 20
b,  5/20/2019, 20
c,  5/20/2019, 20

​x
 
id, date, valuea,  5/22/2019, 22b,  5/22/2019, 22c,  5/22/2019, 22a,  5/21/2019, 21b,  5/21/2019, 21c,  5/21/2019, 21a,  5/20/2019, 20b,  5/20/2019, 20c,  5/20/2019, 20​

Want to get ID and value of current (latest) and previous date:

id, date, date-1
a, 22, 21
b, 22, 21
c, 22, 21

 
id, date, date-1a, 22, 21b, 22, 21c, 22, 21​

So far I have some parts but need only 1 ID per row (this bellow give more than 1 row for each ID – it will show for 5/20 date in the next row for the same ID which is wrong):

select 
    id, value,
    lag(value, 1, 0) over (partition by id order by date ) as "date - 1"
from 
    table1

 
select     id, value,    lag(value, 1, 0) over (partition by id order by date ) as "date - 1"from     table1​

How to achieve that?

Answer

you can try like below

with cte as
(
select 
    id, value,
    lag(value, 1, 0) over (partition by id order by date ) as "date - 1",
    lag(value, 2, 0) over (partition by id order by date ) as "date - 2",
    row_number() over(partition by id order by date desc) rn
from 
    table1
) select * from cte where rn=1

 
with cte as(select     id, value,    lag(value, 1, 0) over (partition by id order by date ) as "date - 1",    lag(value, 2, 0) over (partition by id order by date ) as "date - 2",    row_number() over(partition by id order by date desc) rnfrom     table1) select * from cte where rn=1​

Advertisement

Answer