SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement?

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
SELECT p.FirstName
FROM Person.Person p
ORDER BY p.BusinessEntityID

Now let us use the new Analytic Function from SQL Server 2012 to get previous and next row values.

LAG(p.FirstName) OVER (ORDER BY p.BusinessEntityID) PreviousValue,
LEAD(p.FirstName) OVER (ORDER BY p.BusinessEntityID) NextValue
FROM Person.Person p

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:

rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
FROM Person.Person p
prev.FirstName PreviousValue,
nex.FirstName NextValue
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1

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 (http://blog.SQLAuthority.com)

22 thoughts on “SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement?

    • 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.


  1. Pingback: SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 2 | Journey to SQL Authority with Pinal Dave

  2. Pingback: SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 3 | Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 4 | Journey to SQL Authority with Pinal Dave

  4. 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


  5. 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


  6. 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],
    (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.


  7. 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]
      DECLARE @acao nvarchar(12)
      DECLARE TodosRegistros CURSOR FOR
      SELECT DISTINCT Acao FROM Historico_Completo
      OPEN TodosRegistros
      FETCH NEXT FROM TodosRegistros INTO @acao

      SELECT ID, Fechamento INTO temp1 FROM [actual table] WHERE Acao = @acao

      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)


      FETCH NEXT FROM TodosRegistros INTO @acao
      CLOSE TodosRegistros
      DEALLOCATE TodosRegistros

      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.


  8. 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!


  9. 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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s