SQL Query to get next and previous 3 records based on condition in SQL Server

Last Reply one year ago By dharmendr

Posted one year ago

hi developers ,

i want to get the next 3 records and next previous records.

there is two buttons is there on my web page . if user click the next button means it will get next nearby three records .

if the user click the previous button means  it will get previous nearby three records . 

i am having record DesignationId of , now am in 5 record,

i mean the last binded record is 5

1.if i click next button means it will hit to the database and get DesignationId of Next 5 -->6,7,8 th records by DesignationId

2.if i click Previous button means it will hit to the database and get DesignationId of Next 4 -->4,3,2 th records by DesignationId.

also if the previous or next record is deleted or value not matched means it will get the near by of previous or next like last record is 5 , am click Next = 5 --> 6,7,9 and like last record is 5 , am click Previous= 5 --> 4,2,1

this is my requirement , i am trying but cant able to get the exact output my code is

 select *, desg.DesignationID, desg.Category, desg.SubCategory, desg.Designation,desg.WebDescription,
 desg.Detaileddescription, desg.ImagetoWeb, desg.GiftAmount from Pledge_Designation desg inner join
 Pledge_DesignationCategoryMaster cat on cat.CategoryCode = desg.Category inner join
 Pledge_DesignationSubCategoryMaster sub on sub.SubCategoryCode = desg.SubCategory where
 desg.Category = 13 and sub.SubCategoryCode != 1  and desg.PublishToWeb = 1 and MarkAsgift = 1 and  DesignationID  BETWEEN '2337' AND '2339' order by CreatedDate desc

thanking you

by

Paul.S

You are viewing reply posted by: dharmendr one year ago.
Posted one year ago Modified on one year ago

Hi paulrajmca,

For this query I have used of NorthWind database that you can download using the link given below.

Download Northwind Database

SQL

DECLARE @Id INT
SET @Id = 5
SELECT TOP 3 ProductID,ProductName,UnitPrice 
FROM [Northwind].[dbo].[Products] 
WHERE ProductID > @Id 
ORDER BY ProductID ASC
GO
SELECT TOP 3 ProductID,ProductName,UnitPrice 
FROM [Northwind].[dbo].[Products] 
WHERE ProductID < @Id 
ORDER BY ProductID DESC

Output

Greter than 5

ProductID ProductName UnitPrice
6 Grandma's Boysenberry Spread 25
7 Uncle Bob's Organic Dried Pears 30
8 Northwoods Cranberry Sauce 40

Less than 5

ProductID ProductName UnitPrice
4 Chef Anton's Cajun Seasoning 22
3 Aniseed Syrup 10
2 Chang 19