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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook