Click here to Skip to main content
15,943,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I hope that made sense.

I want to select a range of records, by Part Number, but instead have 3 previous records, the actual current record, and 3 next records. Sort of like looking at the Jukebox, and you can see the previous page, current page, and next page.

03-380 - Previous
03-390 - current
03-400 - Next Record

I can't even think of the words needed to search for this, been trying for awhile now
Posted

1 solution

http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm[^] should help. You can do queries based on the row number, so you can find the row number of your record, then select based on row number before and after.
 
Share this answer
 
Comments
jkirkerx 27-Jul-12 0:10am    
I read the article, and I see that using RowID is what I'm looking for.

I wrote this, but I'm trying to figure out where to set my PartNumber in the middle as Row 3.

I'm really weak in TSQL, not that good at it.

SELECT RowID, ProductID, PostageImage, PartNumber, ShortDescription, Price, Thumbnail, ActionThumbnail1, ActionThumbnail2, ActionThumbnail3, FlatFee, FlatFeeName, VendorName FROM
(SELECT DISTINCT ProductID, PostageImage, PartNumber, ShortDescription, Price, Thumbnail, ActionThumbnail1, ActionThumbnail2, ActionThumbnail3, FlatFee, FlatFeeName, VendorName, ROW_NUMBER()
OVER(ORDER BY PartNumber DESC) AS RowId FROM ProductInfo T) AS Temp
WHERE RowID BETWEEN 1 AND 5
Christian Graus 27-Jul-12 2:22am    
I am imaging you'd select the rowid in to a variable, then you'd do a select where rowid is between that variable -3 and that variable + 3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900