I am using SQL Server 2008. I would love to be using the latest software but I don't have a say in the matter. I have some data, below is a very simple version of what I am trying to do:

The result I am looking for is the following:

I have tried a few update statements using group by but haven't had much luck. It seems like such a simple thing to do but I am quite the novice in SQL, so any help would be much appreciated!

CodePudding user response：

Do a self LEFT JOIN:

select t1.refNumber, t1.Year, t2.Year AS PrevYear
from tablename t1
left join tablename t2 on t1.year = t2.year   1

                      and t1.refNumber = t2.refNumber

(Since your sample data has the same refNumber for all rows, I'm not sure if you want the and t1.refNumber = t2.refNumber part or not.)

CodePudding user response：

You can use the Lag function to do it like this:

SELECT *, 
       Lag(Year, 1) OVER(ORDER BY primaryKey_or_uniqKey ASC) AS PrevYear
FROM tablename ;

You can read about it here.

CodePudding user response：

I would try something like this:

select a.refNumber, a.Year, b.Year as PrevYear from myTable a left join myTable b on a.Year =  (b.Year 1)