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

Earlier I wrote a blog post SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement?. Right after the blog post was published I received an email from SQL Server users who have no access to CTE and wanted me to help him out with the solution as well. Absolutely, Here is the solution for the anyone who is using SQL Server 2005 and does not use CTE.

I strongly suggest you read my earlier blog post before continuing this blog post as they are related to each other. 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

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 2005 and later versions. Here is the solution for the same.

CREATE TABLE #TempTable (rownum INT, FirstName VARCHAR(256));
INSERT INTO #TempTable (rownum, FirstName)
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
p.FirstName
FROM Person.Person p;
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.

UPDATE: I had a few inaccuracies in the blog post, which is corrected based on the feedback of users. Thanks!

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. As other people have already pointed out, this definitely doesn’t work on SQL Server 2000. SQL server 2000 users have no access to CTEs and ROW_NUMBER, since both were introduced in SQL Server 2005.

  2. Nice post Pinal Dave, really enjoy this post.

    But i have one doubt. row_number() is not work in sql 2000, its working in 2005 and Above version of SQL. i think so. May be i am wrong. please correct me if i am wrong.

    thanks and regards

    Arun Vasu

    DSRC Pvt Ltd

    Chennai.

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

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

  5. i worte this command
    SELECT ‘SELECT COUNT(*) FROM ‘+name ,* FROM SYS.all_objects WHERE type_desc = ‘USER_TABLE’ ORDER BY name
    now i do not want to run count command manually i want to run this command atutomatically and give me reulst
    how i do this?

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