CODEWITHSUNDEEP
sqlsql-serversql-server-2008
UnKnown777
UnKnown777

Reputation: 59

Previous row value group by primary key in SQL

I want to get the columns

[QB Revision History],[Pervious QB Revision History]

grouping them based on the column

Code

But I'm not able to do the same. I'm getting these 2 columns 

[QB Revision History],[Pervious QB Revision History]

by using the property

ROW_NUMBER()


select ROW_NUMBER()OVER( 
               ORDER BY CONVERT(DateTime, '01-'+t1.[MonthTitle]) asc) AS rownum,
       t1.[MonthTitle],t1.[Code],t1.[QB Revision History].
into #temp21 
from tbl_SampleQBSizeCDP t1 
group by Code,[MonthTitle],[QB Revision History]

I was trying with this query but it didn't give the expected result.

select  t1.*,t2.[QB Revision History] as [Pervious QB Revision History] 
#into #temp22 from #temp21 t1, #temp21 t2  
where  t1.Code=t2.Code and t1.rownum>t2.rownum

Can anyone help me in solving this issue? Input: rownum MonthTitle Code QB Revision History 1 Mar-16 DFSAWGTESTQB51010 New 9 Apr-16 DFSAWGTESTQB51010 R1 11 May-16 DFSAWGTESTQB51010 Revamp

Output: rownum MonthTitle Code QB Revision History Pervious QB Revision History 9 Apr-16 DFSAWGTESTQB51010 R1 New 11 May-16 DFSAWGTESTQB51010 Revamp New 11 May-16 DFSAWGTESTQB51010 Revamp R1

Expected Output:

rownum MonthTitle Code QB Revision History Pervious QB Revision History 9 Apr-16 DFSAWGTESTQB51010 R1 New 11 May-16 DFSAWGTESTQB51010 Revamp R1

Upvotes: 0

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If I understand correctly, the function that you want is LAG(), not ROW_NUMBER():

select t1.[MonthTitle], t1.[Code], t1.[QB Revision History],
       LAG([QB Revision History]) OVER (ORDER BY CONVERT(DateTime, '01-'+t1.[MonthTitle]
                                       ) as prev_revisition_history
 into #temp21
from tbl_SampleQBSizeCDP t1;

I am not sure what the order by is for. Your SELECT has no aggregation functions. Without sample data and desired results, it seems unnecessary.

EDIT:

In SQL Server 2008, you can use outer apply:

select t1.[MonthTitle], t1.[Code], t1.[QB Revision History],
       t2.QB Revision History]) as prev_revisition_history
into #temp21
from tbl_SampleQBSizeCDP t1 outer apply
     (select top 1 t2.*
      from tbl_SampleQBSizeCDP t2
      where CONVERT(DateTime, '01-'+t2.[MonthTitle]) < CONVERT(DateTime, '01-'+t1.[MonthTitle])
      order by CONVERT(DateTime, '01-'+t2.[MonthTitle]) desc
     ) t2

Upvotes: 1

Related Questions