CODEWITHSUNDEEP
sqlsql-servert-sql
Krishna
Krishna

Reputation: 37

How to get previous row data in sql server

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.

Upvotes: 3

Views: 14575

Answers (4)

Krishna
Krishna

Reputation: 37

Thank you all for the replies. By using the lag function with partition I got the expected result. I missed to used partition previously and due to that I was getting wrong results.

Upvotes: -2

James
James

Reputation: 386

If you are on 2008 or earlier, try this:

    select t1.col1, t1.col2, t1.col3, t2.col3 as col4
    from table1 t1
    left join table1 t2 on t1.col1 = t2.col1 and t1.col2 - 1 = t2.col2

the lag() function is the bee's knees, though. Use that, if you can.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86706

Assuming SQL Server 2012 or newer...

SELECT
  *,
  LAG(col3) OVER (PARTITION BY col1 ORDER BY col2) AS col4
FROM
  yourTable

If you're on SQL Server 2008 or older...

SELECT
  *,
  (
     SELECT TOP(1) previous.col3
       FROM yourTable   AS previous
      WHERE previous.col1 = yourTable.col1
        AND previous.col2 < yourTable.col2
   ORDER BY previous.col2 DESC
  )
    AS col4
FROM
  yourTable

Upvotes: 4

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 5

Related Questions