How to get previous row data in sql server

Question

I would like to get the data from previous row. I have used LAG function but did not get the expected result.

Table:-

col1  col2  col3
ABCD    1   Y
ABCD    2   N
ABCD    3   N
EFGH    4   N
EFGH    5   Y
EFGH    6   N
XXXX    7   Y

Expected result

col1 col2 col3  col4
ABCD    1   A   NULL
ABCD    2   B   A
ABCD    3   C   B
EFGH    4   A   NULL
EFGH    5   B   A
EFGH    6   E   B
XXXX    7   F   NULL

Col4 should hold the data from previous row grouping by the value in Col1. Please let me know how can this be achieved.

Answer 1

Use lag() function

select *, lag(col3) over (partition by col1 order by col2) as col4
from table t;

However You can also use subquery if your SQL doesn't have LAG()

select *,   
        (select top 1 col3
         from table
         where col1 = t.col1 and col2 < t.col2
         order by col2 desc
        ) as col4
from table t;