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
Really useful…. Thanks you so much
hello How can I get the next value on the condition of where?
example where productID = 20 but I want 21
thank you
Want to update rows which are same continuously one by one. Like,
Sno Name Activity StartTime EndTime
1 ABC A 8:00:00 9:45:00
1 ABC B 8:00:38 8:16:39
1 ABC A 8:16:39 9:45:00
2 ABC C 9:45:00 10:00:00
3 ABC D 10:00:00 12:00:00
3 ABC E 10:00:00 10:26:38
3 ABC C 10:26:38 10:30:41
3 ABC C 10:30:47 10:32:49
3 ABC C 10:33:03 10:45:46
You see in last Activity ‘C’ is coming continously 3 times. I want that row only once and need to update End Time to last row EndTime.
This blog helped me quickly understand the LAG() function to move data down a row. Thanks!
Thank You :) This information helped me a lot!
If I had a table with a negetively incrementing Identify (as in the identity increment is set to -n) then how could i use this to work out that the identity column is incrementing in negative increments?
Or, do you know of another way to work out what the Seed and Increment (in particular) values were set to originally on a column with the Identity constraint set?
The problem i have is that if i were to use the Lag command, it requires the use of OVER which requires the use of an ‘ORDER BY’ statement.
If I use ORDER BY then, yes, i can work out the increments between IDs, however, that only works if 1) there are no missing values and 2) they’re all unique.
I’m trying to work out an easy way of establishing the configuration of a source table’s Identity settings such that i can replicate this on a Target table…I’ve been unable to establish one thus far…i am quite new at this to be fair :-)