The first email I read this morning had only one statement in it, and it gave me an idea to write this blog post.
“How to access Previous Row Value and Next Row Value in SELECT statement?”
Very interesting question indeed. The matter of the fact, I have written about this in my earlier blog Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012. Let us quickly understand it in it with the help of script.
For example here is the column where we want to get access to the previous row and the next row in SELECT statement.
USE AdventureWorks2012
GO
SELECT p.FirstName
FROM Person.Person p
ORDER BY p.BusinessEntityID
GO
Now let us use the new Analytic Function from SQL Server 2012 to get previous and next row values.
SELECT
LAG(p.FirstName) OVER (ORDER BY p.BusinessEntityID) PreviousValue,
p.FirstName,
LEAD(p.FirstName) OVER (ORDER BY p.BusinessEntityID) NextValue
FROM Person.Person p
GO
You can see it is very simple to get Previous and Next value with the help of Lead and Lag Function in SQL Server.
However, if you are using an earlier version of SQL Server which does not support LEAD and LAG function we can do the following:
WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
p.FirstName
FROM Person.Person p
)
SELECT
prev.FirstName PreviousValue,
CTE.FirstName,
nex.FirstName NextValue
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
GO
Though the result will be almost same as earlier queries but the performance will be very bad. I suggest you use SQL Server 2012 and the query related to the same.
Reference: Pinal Dave (https://blog.sqlauthority.com)
56 Comments. Leave new
HI I have stuck at this point where I need to show previous row record if current record is null.
I have achieved for it for 1 row.
but what i need is if next 3 rows have null value than all records should get filled by previuos row where record exist.
I am using 2008R2
Thanks in advance
It is useful for me. Thanks a lot!
I am trying following query
SELECT LAG(U_NAME) OVER (ORDER BY U_NAME)prev ,u_name,LEAD(U_name ) OVER (ORDER BY U_name) NextValue FROM LOGIN WHERE U_NAME=’sarim.hasan’
I am trying to access previous and next name user name form login but values in prev and nextvalue in output are coming NULL
After a couple hours of research and finding only SQL Server 2012 solutions, I finally found a solution that works for 2008 and can be expanded to GROUP/ORDER by additional fields very easily.
Thank you very much for this informative post!!
–2008
WITH CTE AS
( SELECT ROW_NUMBER() OVER (ORDER BY tableX.Document, tableX.Revision) AS RowNum, tableX.Document, tableX.Revision
FROM (SELECT DISTINCT Document, Revision FROM tableX) tableX
)
SELECT
CTE.Document
,CTE.Revision
,prev.Revision AS [Previous Revision]
,nex.Revision AS [Next Revision]
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum – 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
ORDER BY CTE.Document, CTE.Revision
–2012
SELECT
Document
,Revision
,LAG(Revision) OVER (ORDER BY Document, Revision) AS [Previous Revision]
,LEAD(Revision) OVER (ORDER BY Document, Revision) AS [Next Revision]
FROM (SELECT DISTINCT Document, Revision FROM tableX) tableX
ORDER BY Document, Revision
How to use this when where clause is present and it returns single row
SELECT * FROM
(
SELECT p.BusinessEntityID,
LAG(p.FirstName) OVER (ORDER BY p.BusinessEntityID) PreviousValue,
p.FirstName,
LEAD(p.FirstName) OVER (ORDER BY p.BusinessEntityID) NextValue
FROM Person.Person p ) M WHERE BusinessEntityID = 6
OR
SELECT * FROM
(
SELECT
LAG(p.FirstName) OVER (ORDER BY p.BusinessEntityID) PreviousValue,
p.FirstName,
LEAD(p.FirstName) OVER (ORDER BY p.BusinessEntityID) NextValue
FROM Person.Person p ) M WHERE FirstName = ‘Jossef’
when just want to know the previous, I am assuming when there are many instances (more than 2), we need to use a MAX clause to start at the maximum and look for previous.
this site makes me look like a champ!
Thanks
You helped me so much, as usual :-)
Thanks
I am glad to see this comment Jakub.
Very useful. Thank you very very much.
My pleasure Vijaya.
“I suggest you use SQL Server 2012”. Gee, thanks. Unfortunately, like most developers, I have no control over the environment. If I asked my boss to upgrade because it would make this function faster, they would laugh me out of their office.
I understand in that case, you will have to write custom code.
utilizing this code:
DECLARE @ClientID int
DECLARE @StartWhen date
DECLARE @StopWhen date
DECLARE @Archive int
DECLARE contact_cursor CURSOR FOR
Select ClientID, StartWhen, StopWhen, Archive from [WES].[rpt].[DataConfidentialityInferred_History] order by ClientID, StartWhen
OPEN contact_cursor;
FETCH NEXT FROM contact_cursor INTO @ClientID,@StartWhen, @StopWhen, @Archive;
WHILE @@FETCH_STATUS = 0
BEGIN
if @Archive = 0 set @StopWhen = ‘9999-12-31’
if @Archive = 1 set @StopWhen = ( SELECT top 1 LEAD(p.StartWhen) OVER (ORDER BY p.ClientID, p.StartWhen) NextValue
FROM [WES].[rpt].[DataConfidentialityInferred_History] p
where ClientID=@ClientID
–and StartWhen=@Startwhen
)
print ‘ClientID:’+ convert(varchar,@ClientID) + ‘ StartWhen:’+ convert(varchar,@StartWhen) + ‘ StopWhen:’ + convert(varchar,@StopWhen) + ‘ Archive:’ + convert(varchar,@Archive)
FETCH NEXT FROM contact_cursor INTO @ClientID,@StartWhen, @StopWhen, @Archive;
END;
CLOSE contact_cursor;
DEALLOCATE contact_cursor;
works well until I get to this:
ClientID:1787 StartWhen:2017-04-20 StopWhen:2017-04-26 Archive:1
ClientID:1787 StartWhen:2017-04-26 StopWhen:2017-04-26 Archive:1
ClientID:1787 StartWhen:2017-05-01 StopWhen:9999-12-31 Archive:0
where I need the second ClientID 1787 to StopWhen on 05-01, not the 04-26
Help!
Thanks Pinal for solving my Problem, I have spent almost more than six hours for solution, you have made my day as always,
Hi folks,
What if we need to fetch next to next row using lead function in sql server 2014?
Thanks in advance.
This is a great solution and it did exactly what I needed. Thanks for sharing.
Hi,
I have the data set as shown below
Date Month From To Total SerialNumber
2011-05-16 Jul NULL 488.04 2440.18 1
2011-05-16 Aug NULL 488.04 2440.18 2
2011-05-16 Sep NULL 488.04 2440.18 3
2011-05-16 Oct NULL 488.04 2440.18 4
2011-05-16 Nov 2425.73 488.04 2440.18 5
2011-05-16 Nov 1005.00 488.04 2440.18 6
similar set of data with different date and the serialnumber again starts from 1
Currently the total column is holding same amount, but it should be derived with below condition
For serialnumber 1 it should be same and that should be the starting value and for second record, the total should be (total(previous rec) + to(current rec) – from(current rec)) and this should continue until the last serial number record for a given date.
Could you please help me out in how to write the query to derive total as per the requirement
Thanks for this. I wasn’t aware of this function, but it did exactly what I needed
Hi Pinal,
After i view your code, I just realise that i need to replace my code from “inner join” to “left join” and now everything is okay,
Thank you so much,
You are a hero :)
You are very kind with your words.
Thanks so much for the very simple example. It solved my problem within five minutes!
Thanku so much for helping me.
How can I get running number with 1,2,3 including current year i.e. 1/2019, 2/2019.3/2019 ….. and when year change it should be 1/2020, 2/2020,3/2020 ………….. so on.. actually I need this to show on grid view..