June 24, 2009

Find the Next and Previous Records in a SQL Server Table




I was solving a strange requirement a few days ago – well not strange for my client though. The client had a Product table. They wanted a query which would accept a Product ID and display the Next and Previous Products of that Product ID listed in that table or a result set. Now I may be wrong, but queries like this are not really solved in T-SQL; rather a front end UI like the GridView is capable of navigating through the records.

However if you intend doing it in T-SQL, then here’s how we can retrieve the Next and Previous Records of a given record

-- Create Sample Table
DECLARE @TT table
(
ProductID int,
CategoryGroupID int,
CategoryGroupName varchar(10)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 101, 1, 'AA')
INSERT INTO @TT VALUES ( 203, 1, 'AA');
INSERT INTO @TT VALUES ( 305, 1, 'AA');
INSERT INTO @TT VALUES ( 403, 2, 'BB');
INSERT INTO @TT VALUES ( 553, 2, 'BB');
INSERT INTO @TT VALUES ( 634, 2, 'BB');
INSERT INTO @TT VALUES ( 744, 2, 'BB');
INSERT INTO @TT VALUES ( 838, 3, 'CC');
INSERT INTO @TT VALUES ( 939, 3, 'CC');
INSERT INTO @TT VALUES ( 1245, 3, 'CC');
INSERT INTO @TT VALUES ( 1341, 3, 'CC');
INSERT INTO @TT VALUES ( 1452, 3, 'CC');
INSERT INTO @TT VALUES ( 1565, 4, 'DD');

SELECT
(SELECT MAX(ProductID)
FROM @TT Prod1
WHERE Prod1.ProductID < Prod2.ProductID ) as PreviousProductID,
(SELECT MIN(ProductID)
FROM @TT Prod1
WHERE Prod1.ProductID > Prod2.ProductID ) as NextProductID
FROM @TT Prod2
WHERE ProductID = 744

OUTPUT

image


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

1 Response to "Find the Next and Previous Records in a SQL Server Table"
  1. Mandy said...
    January 13, 2010 at 11:26 PM

    Great!!!!!!!!!!!

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions