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

Earlier I wrote a blog post SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? and SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 2. In part 2 of the blog post, I wanted to write a solution which works with SQL Server 2000. In the solution I removed CTE but I forgot the detail that I SQL Server 2000 does not support RowNumber function as well. Thanks to smart blog readers who caught the error and immediately pointed that out in the comment area. Thank you so much for it. In this blog post, I will now demonstrate how to come up with the solution for previous row and next row in SQL Server 2000 version.  The question was that, how to get access to Previous Row and Next Row in the SELECT statement.

Let us assume that we have following SQL Query.

USE AdventureWorks2012
GO
SELECT p.FirstName
FROM Person.Person p
ORDER BY p.BusinessEntityID
GO

SQL SERVER - How to Access the Previous Row and Next Row value in SELECT statement? - Part 3 leadlagfunction

What we want is that in the same SELECT statement the previous row and next row should be listed. Additionally the solution should support SQL Server 2000 and later versions. Here is the solution for the same.

USE AdventureWorks2012
GO
SELECT
rownum = IDENTITY(INT, 1,1),
p.FirstName
INTO #TempTable
FROM Person.Person p
ORDER BY p.BusinessEntityID;
SELECT
prev.FirstName PreviousValue,
TT.FirstName,
nex.FirstName NextValue
FROM #TempTable TT
LEFT JOIN #TempTable prev ON prev.rownum = TT.rownum - 1
LEFT JOIN #TempTable nex ON nex.rownum = TT.rownum + 1;
GO

In the above example we have used Temp Table and with the help of Temp Table we have built our solution, which returns following result.

SQL SERVER - How to Access the Previous Row and Next Row value in SELECT statement? - Part 3 leadlagfunction1

I hope today I have got the answer correct with the help of Identity Function and Temp Table.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL Contest – Hint for Identify the Database Celebrity
Next Post
SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 4

Related Posts

6 Comments. Leave new

  • Wouldn’t a better/faster solution be to use a CTE and the rownumber function?

    Reply
  • Hi..Yous code is working fine but you have to drop #TempTable at end of Query.

    Reply
  • Akhtar Eimon
    June 23, 2016 7:30 am

    Nice post Pinal. But I think rownum is not guaranteed to generate proper sequence here, as you have used order by clause.
    rownum gets generated and inserted into the temp table before the order by clause gets applied.

    Create table #tempTable(
    rownum int not null Identity(1,1),
    FirstName varchar(50)
    )

    insert into #TempTable
    (rownum,FirstName)
    SELECT
    rownum = IDENTITY(INT, 1,1),
    p.FirstName

    FROM Person.Person p
    ORDER BY p.BusinessEntityID;
    SELECT
    prev.FirstName PreviousValue,
    TT.FirstName,
    nex.FirstName NextValue
    FROM #TempTable TT
    LEFT JOIN #TempTable prev ON prev.rownum = TT.rownum – 1
    LEFT JOIN #TempTable nex ON nex.rownum = TT.rownum + 1;

    This should guarantee proper rownum sequence generation. Check the link below for reference.
    https://support.microsoft.com/en-us/help/273586

    Reply
  • Akhtar Eimon
    June 23, 2016 7:33 am

    Correction–>

    Create table #tempTable(
    rownum int not null Identity(1,1),
    FirstName varchar(50)
    )

    insert into #TempTable
    (FirstName)
    SELECT
    p.FirstName

    FROM Person.Person p
    ORDER BY p.BusinessEntityID;
    SELECT
    prev.FirstName PreviousValue,
    TT.FirstName,
    nex.FirstName NextValue
    FROM #TempTable TT
    LEFT JOIN #TempTable prev ON prev.rownum = TT.rownum – 1
    LEFT JOIN #TempTable nex ON nex.rownum = TT.rownum + 1;

    Reply

Leave a Reply

Menu