Unable to retrieve data with LAG function in SQL Server

Question

Not sure how to get data with lag function.

I have this data:

date                        value   id
---------------------------------------
2019-03-01 00:00:00.000     1       a
2019-02-28 00:00:00.000     2       a
2019-02-27 00:00:00.000     3       a
2019-02-26 00:00:00.000     4       a
2019-03-01 00:00:00.000     4       b
2019-02-28 00:00:00.000     3       b
2019-02-27 00:00:00.000     2       b
2019-02-26 00:00:00.000     1       b

I need lag 1 and lag2 value and for each id only 1 row

 id  value lag1 lag2
--------------------
 a    1     2    3
 b    4     3    2

My query is

select 
    id, date, value, 
    lag(value, 1) over (partition by id order by date),
    lag(value, 2) over (partition by id order by date) 
from 
    data;

But I am getting more than 1 row and for every row lag is getting calculated. I know the function behaves that way but unable to get desired output. Used another query

select top 1 
from 
    (select 
         id, date, value, 
         lag(value, 1) over (partition by id order by date),
         lag(value, 2) over (partition by id order by date)
     from 
         data)

This is returning only 1 row but I have several id's. I need to limit 1 row for each id. Any help is appreciated

Answer 1

lag() is a window function, so it doesn't change the number of rows. You need some filtering to do that.

How about this?

select id, date, value, value_1, value_2
from (select id, date, value,
             lag(value, 1) over (partition by id order by 
date) as value_1,
             lag(value, 2) over (partition by id order by date) as value_2,
             row_number() over (partition by id order by date desc) as seqnum
      from data
     ) d
where seqnum = 1