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
1 comment:
Great!!!!!!!!!!!
Post a Comment