TSQL , Select records by ID, 3 previous and 3 next, total of 7

Question

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 26-Jul-12 16:09pm

jkirkerx

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Christian Graus · Answer 1 · 2012-07-26T16:13:00

Solution 1

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.

Posted 26-Jul-12 16:13pm

Christian Graus

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