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
one more nice article sir simply superb very clear to understand
In what kind of a real life scenario would I use such a function?
Perhaps you have a table of events with a timestamp when the event occurred. For any given instance of an event (let’s say a failed login attempt), you can use LAG to determine how long it has been since the previous occurrence and execute business logic (like blocking the IP from trying again). Or looking at a sales database, you can use these functions to view a customer’s first/newest/largest order without writing multiple queries.
I have a list of codes and when the code became effective. I use this to determine the range by finding the next effective date and providing a range to query when a particular code was valid. It is in SQL Server 2008 R2 and greatly reduced my processing time.
Thanks Pinal. Good stuff.
I have a table with depth measurements, this way I can determine the interval between the previous/current/next depth reference. Very handy and quick way to do.
Hi,
Somehow these 2 functions are not working in my case:
Following are the details:
Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.7601.17514
Microsoft .NET Framework 4.0.30319.18052
Operating System 6.1.7601
thnx for sharing this information.
What to do if on sql server 2008 r2
Some of us are still on SQL Server 2008 R2, then what? It’s nice to have 12 but in reality it’s not the case.
Your CTE approach worked great in my SQL2008R2, thanks! (I wanted to retrieve the timestamp of the next event in a series of timestamped events, and create an event window.)
Nice and clear code. Thanks! :)
how to find out the physical location of a row in sql server 2012 .
%%physLoc%% is not working in 2012. what is alternative of %%physLoc%% in sql server 2012
This will show all the rows data. How to get one row answer in which the prev and next row values will be included with the value in the where clause.
Well this is what I am doing.
SELECT (SELECT IMAGENAME FROM dbo.ImageDetails WHERE ImageID = IMG.imageid – 1) [prev],
Img.ImageName,
(SELECT IMAGENAME FROM dbo.ImageDetails WHERE ImageID = IMG.imageid + 1) [next]
FROM dbo.ImageDetails Img
WHERE Img.ImageName = ‘SqlServer2012.jpg’
I am using an image slider where the prev and next arrows will have.
Any suggestions.
Hi. Indeed very nice article. But I need a little help in here.
I have this statement which works fine:
SELECT (((aux1.Fechamento/(Lag(aux1.Fechamento) OVER (ORDER BY aux1.ID)))-1)*100) as Formula From aux1
It’s an arithmetic calculation = ((PriceToday/PriceYesterday)-1)*100). It does work.
Then I want to use it in an Update statement, passing a parameter (Acao) like this:
UPDATE aux1
SET Porcentagem = query.Formula FROM (
SELECT (((aux1.Fechamento/(Lag(aux1.Fechamento) OVER (ORDER BY aux1.ID)))-1)*100) as Formula From aux1) query Where Acao = ‘CGAS5’
The table aux1 has 5 columns: ID (int), Data (datetime), Acao (nvarchar(12)), Fechamento (float NOT NULL) and Porcentagem (float NULL).
I get this table from a larger one, by doing this:
SELECT ID, Data, Acao, Fechamento, Porcentagem INTO aux1 FROM [Larger_Table] Where Acao = ‘CGAS5’. So my aux1 table ALREADY has only the elements to performe the calculus.
However, it returns the field ‘Porcentagem’ all zero (0).
Here are some results:
ID Data Acao Fechamento Porcentagem
59 2007-01-02 CGAS5 362 0
369 2007-01-03 CGAS5 354,01 0
671 2007-01-04 CGAS5 357,19 0
961 2007-01-05 CGAS5 347 0
1247 2007-01-08 CGAS5 348,5 0
1538 2007-01-09 CGAS5 349 0
1825 2007-01-10 CGAS5 354 0
2114 2007-01-11 CGAS5 351,01 0
2401 2007-01-12 CGAS5 347,99 0
2708 2007-01-15 CGAS5 350 0
3021 2007-01-16 CGAS5 349,4 0
Am my missing something about the “Where” Clause?
Thanks in advance.
Hi everyone.
I get to solve my problem using a very slow solution.
Here it is:
CREATE PROCEDURE [dbo].[Atualiza_Porcentagem_LAG_COM_TABLE]
AS
BEGIN
DECLARE @acao nvarchar(12)
DECLARE TodosRegistros CURSOR FOR
SELECT DISTINCT Acao FROM Historico_Completo
OPEN TodosRegistros
FETCH NEXT FROM TodosRegistros INTO @acao
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT ID, Fechamento INTO temp1 FROM [actual table] WHERE Acao = @acao
— THIS IS A BIIIIIG PROBLEM…
UPDATE temp2
SET Porcentagem = alias.Formula FROM (SELECT ID, isnull(((Fechamento/LAG(Fechamento) OVER (ORDER BY ID))-1)*100,0) as Formula FROM temp1) as alias WHERE (temp2.id = alias.id)
DROP TABLE temp1 — THIS ANOTHER BIIIIG PROBLEM!!
FETCH NEXT FROM TodosRegistros INTO @acao
END
CLOSE TodosRegistros
DEALLOCATE TodosRegistros
END
I update temporary tables and not the [actual table] above.
Table aux2 has exactly the same field names as [actual table].
But it took 2min45sec to process 470 k records.
Will anyone help me to sort it out on HOW to insert the @acao parameter inside the UPDATE statement, in the Select clause?
This is a home made solution not to be used in real scenarios, because when it comes to a DW environment, I would update the recently added record only. The ones already calculated would be preserved.
Thanks in advance.
Regards.
I forgot that when I remove the FROM part, it gives me this error: Subquery returned more than 1 value.
Missing something in the Where clause…
hi. this article helped me to get a single row, I want to get previous 12 rows.. any suggestions.. Thanks in Advance
Thanks for the post. I was just wondering exactly this (how to get previous, next row values).
Your post showed up #1 in Google like so many other times.
Yeah for Pinal Dave!
Glad it helped @Ira
Hi Pinal Dave,
Thank you very much for your sharing information.
It’s very useful for me.
However, please suggestion for me one case bellow.
1. I have master table contain all item of electric:
– SQL command:
SELECT Name_EL
FROM EL_PowerMeter
WHERE Status = ‘on’;
– Result:
Name_EL
MDB-53 MCCB-6 KWH
L-5-MO-2(N) KWH
MCCB M-M2OF-2 KWH
MDB-54 MCCB-6 KWH
MCCB M1-14 KWH
2. Follow data from master table. I select detail electric using each 5min/time follow each item.
– SQL command
SELECT item_name,
Time_elec,
Item_Value
FROM ElectricMachineLink
WHERE item_name in (
SELECT Name_EL
FROM EL_PowerMeter
WHERE Status = ‘on’)
ORDER by item_name,Time_elec
– Result
– Data
item_name Time_elec Item_Value
L-5-MO-2(N) KWH 10/16/2016 22:05:00 302133
L-5-MO-2(N) KWH 10/16/2016 22:10:00 302133
L-5-MO-2(N) KWH 10/16/2016 22:15:00 302133
L-5-MO-2(N) KWH 10/16/2016 22:20:00 302133
L-5-MO-2(N) KWH 10/16/2016 22:25:00 302133
L-5-MO-2(N) KWH 10/16/2016 22:30:00 302133
L-5-MO-2(N) KWH 10/16/2016 22:35:00 302134
L-5-MO-2(N) KWH 10/16/2016 22:40:00 302134
L-5-MO-2(N) KWH 10/16/2016 22:45:00 302134
L-5-MO-2(N) KWH 10/16/2016 22:50:00 302134
L-5-MO-2(N) KWH 10/16/2016 22:55:00 302134
L-5-MO-2(N) KWH 10/16/2016 23:00:00 302134
L-5-MO-2(N) KWH 10/16/2016 23:05:00 302134
So now I want to make minus current time – previous time ( each item) I using:
SQL command
SELECT distinct item_name,
time_elec, Item_Value,
Item_Value – Lag(Item_Value, 1, 0)
OVER (
ORDER BY Item_Value) AS Number_Use
FROM ElectricMachineLink
where item_name in (
SELECT Name_EL
FROM EL_PowerMeter
WHERE Status = ‘on’)
order by item_name,time_elec
item_name Time_elec Item_Value Number_Use
L-5-MO-2(N) KWH 10/16/2016 22:00:00 302133 0
L-5-MO-2(N) KWH 10/16/2016 22:05:00 302133 0
L-5-MO-2(N) KWH 10/16/2016 22:10:00 302133 0
L-5-MO-2(N) KWH 10/16/2016 22:15:00 302133 0
L-5-MO-2(N) KWH 10/16/2016 22:20:00 302133 0
L-5-MO-2(N) KWH 10/16/2016 22:25:00 302133 0
L-5-MO-2(N) KWH 10/16/2016 22:30:00 302133 0
L-5-MO-2(N) KWH 10/16/2016 22:35:00 302134 0
L-5-MO-2(N) KWH 10/16/2016 22:35:00 302134 1
L-5-MO-2(N) KWH 10/16/2016 22:40:00 302134 0
L-5-MO-2(N) KWH 10/16/2016 22:45:00 302134 0
But data show incorrect.
Ex: at 22:35:00 data send 2 time. However only minus with previous row. And if I select 1 exactly data. Item_Value and Number_Use same.
– SQL command
SELECT distinct item_name,
time_elec, Item_Value,
Item_Value – Lag(Item_Value, 1, 0)
OVER (
ORDER BY Item_Value) AS Number_Use
FROM ElectricMachineLink
where item_name =’L-5-MO-2(N) KWH’ and Time =’10/16/2016 22:35:00′
item_name Time_elec Item_Value Number_Use
L-5-MO-2(N) KWH 10/16/2016 22:35:00 302134 0
L-5-MO-2(N) KWH 10/16/2016 22:35:00 302134 302134
Please support me to show 1 row and value = 1 is OK
item_name Time_elec Item_Value Number_Use
L-5-MO-2(N) KWH 10/16/2016 22:35:00 302134 1
I hope can receive answer ASAP.
Thank you!
I had already written a query with two self-joins to get a field from the previous and next records. For those interested in the business reason – we have a report of invoices and invoice lines and recently there had been a few cases detected where a line from one invoice was appearing amongst the lines of another. So for example for invoice 23 the invoice lines contained an invoice number of 23, 23, 24, 23, 23, 23. My self-join query was attempting to detect cases where the invoice on the line before and the invoice on the line after were the same (23), but different from the invoice on the current line (24).
My query worked but it was running like a dog. By restricting it to just a single day it managed to output one record in 7 minutes 50 seconds but I needed to query everything this year. I was dubious whether LAG and LEAD would make any difference because I figured under the hood they’d still have to do the same type of internal work, but then I read Pinal’s closing comment about performance being bad if not using them so I decided to rewrite my query and give them a try.
The query completed in 2 seconds!!! (I’ve been in the IT industry since 1980 so I guess I shouldn’t be surprised by things these days, but this one got me.) Running it for the whole year took 19 seconds.
Nice work Microsoft and thanks for the prompter Pinal.
This is definitely a good example, well written. I do have a question, say you had five rows that were identical in certain columns, how would you refer to the first record since the following four would be considered duplicate? In my case, the duplicates are expected and are ok. Using the CTE, I plan to find all records that match three columns and whatever the first record is, I want to copy the value of three fields from the first record and populate the following records. Here is my current CTE query. Let me know what I would have to do to make it do what I am trying to.
WITH cte AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY Series_Number,ID_Number,System_Selection_Date,[TimeStamp],Existing_Cust,Contacted,Profile_Built,Comments
ORDER BY Series_Number,ID_Number,System_Selection_Date,[TimeStamp],Existing_Cust,Contacted,Profile_Built,Comments ) AS rownum,
Series_Number,ID_Number,System_Selection_Date,[TimeStamp],Existing_Cust,Contacted,Profile_Built,Comments
FROM tblRecords
)
–select * from cte
UPDATE cte SET Comments =’DUPLICATE’ WHERE rownum1
What I want is to match based on Series_Number, ID_Number, and Selection_Date. Records 2 through end should equal record #1 on the Existing_Cust, Contacted, and Profile_Built fields and the comments need to be set to “DUPLICATE”.
This article is great and you made CTE easy to understand. I do have a question though. I am trying to make any duplicate records after the first be marked with three statuses and commented as duplicate. Here is my query:
WITH cte AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY Series_Number,ID_Number,System_Selection_Date,[TimeStamp],Existing_Cust,Contacted,Profile_Built,Comments
ORDER BY Series_Number,ID_Number,System_Selection_Date,[TimeStamp],Existing_Cust,Contacted,Profile_Built,Comments ) AS rownum,
Series_Number,ID_Number,System_Selection_Date,[TimeStamp],Existing_Cust,Contacted,Profile_Built,Comments
FROM tblRecords
)
–select * from cte
UPDATE cte SET Comments =’DUPLICATE’ WHERE rownum1
What I want to do is have it look for Series_Number, ID_Number, System_Selection_Date, if there are more than one, the first record values from Existing_Cust, Contacted, Profile_Built are copied to the duplicates and the comments field gets set as DUPLICATE. Thanks!
My data kind of looks like this:
Series_No IDNumber SelDate TimeStamp EC CM PB
1413903000159 xxxxx7475 2014-12-26 2014-12-26 10:18:44.000 No Yes No
1413903000159 xxxxx7475 2014-12-26 2014-12-26 10:18:44.000 Yes Yes No
1413903000159 xxxxx7475 2014-12-26 2014-12-26 10:18:44.000 No No No
In this case, records 2 and 3 should match the EC, CM, and PB values
1413903000159 xxxxx7475 2014-12-26 2014-12-26 10:18:44.000 No Yes No
1413903000159 xxxxx7475 2014-12-26 2014-12-26 10:18:44.000 No Yes No
1413903000159 xxxxx7475 2014-12-26 2014-12-26 10:18:44.000 No Yes No
Make sense?
@bbcompent1 – Which EC, CM, PB should take precedence?
We are still on SQL Server 2008 R2 so the tip on how to achieve the same outcome as LAG and LEAD using a CTE is very nice. Thank you!