SQL Server Portal

I came across this query many times and most often we need the previous row result in the current row for comparison purposes.  Fortunately, we do have a solution for this problem using self join. But I will share another efficient solution with you using LAG (an analytic function shipped with SQL Server 2012).

Let me create a sample to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
[ID] int,
[Levels] varchar(50)
)
GO

INSERT INTO tbl_sample VALUES (1,'LEVEL 1')
INSERT INTO tbl_sample VALUES (2,'LEVEL 2')
INSERT INTO tbl_sample VALUES (3,'LEVEL 3')
INSERT INTO tbl_sample VALUES (4,'LEVEL 4')
INSERT INTO tbl_sample VALUES (5,'LEVEL 5')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

Let me show you both old and new approaches.

Old Approaches :
Given below is the old approach that we generally develop using self join. This approach you can use in any version of SQL Server.

USE tempdb
GO
SELECT
A.ID
,A.Levels As [Current Level]
,B.Levels AS [Previous Level]
FROM tbl_sample A
LEFT JOIN tbl_sample B ON A.ID=B.ID+1
ORDER BY A.ID
GO
--OUTPUT

New Approaches :
In this approach, you do not need to do self join and make it complicated. You just need to use LAG function and it will calculate the previous result row for you automatically. This approach can be used in SQL Server 2012 and above.

USE tempdb
GO
SELECT
A.ID
,A.Levels As [Current Level]
,LAG(A.levels,1,0) OVER (ORDER BY A.ID) AS [Previous Level]
FROM tbl_sample A
GO
--OUTPUT

Conclusion:
In the above approaches, you can see that the result set are same but the new approaches reduce the complexity and increase the performance.