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

About these ads

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

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

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

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

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

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