Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Unable to figure out...

Status
Not open for further replies.
ermora

ermora

IS-IT--Management
Joined
Apr 5, 2004
Messages
70
Location
US
a query command that will not only return the particular record I'm searching for, but also xx number of records before and after the record found.

For example, lets say I have a Parts table with a field called Part_No. The table is populated with 10,000 records where the Part_No is sequential, from 1 to 10,000.

Now, I search for say Part_No 800 (which happens to be record 800.) What I'm looking to do is return record 800 and records 600-799 (200 records above where record 800 was found) and 801-1000 (200 records below where record 800 was found.)

Is this possible?

Regards
 
Sort by date Sort by votes
Ermora,

I've shown how it's done below. The example uses +/- 5 and only has 100 records, just to save time.
Code: 
CREATE TABLE ERMORA_TEST
(
 ROW_NUMBER SMALLINT IDENTITY(1,1),
 DESCRIPTION VARCHAR(100)
)

--Proof of concept, so 100 rows will suffice
INSERT INTO ERMORA_TEST VALUES ('ROW 1 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 2 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 3 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 4 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 5 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 6 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 7 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 8 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 9 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 10 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 11 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 12 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 13 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 14 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 15 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 16 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 17 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 18 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 19 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 20 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 21 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 22 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 23 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 24 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 25 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 26 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 27 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 28 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 29 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 30 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 31 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 32 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 33 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 34 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 35 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 36 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 37 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 38 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 39 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 40 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 41 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 42 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 43 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 44 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 45 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 46 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 47 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 48 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 49 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 50 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 51 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 52 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 53 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 54 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 55 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 56 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 57 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 58 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 59 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 60 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 61 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 62 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 63 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 64 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 65 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 66 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 67 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 68 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 69 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 70 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 71 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 72 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 73 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 74 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 75 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 76 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 77 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 78 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 79 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 80 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 81 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 82 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 83 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 84 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 85 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 86 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 87 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 88 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 89 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 90 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 91 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 92 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 93 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 94 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 95 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 96 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 97 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 98 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 99 DESCRIPTION')
INSERT INTO ERMORA_TEST VALUES ('ROW 100 DESCRIPTION')

--Confirm correct loading of 100 rows
--SELECT * FROM ERMORA_TEST
--If passed in row_number is 30, and you want 30 plus or minus 5
--(really 200, but this is POC) then

Code: 
SELECT *
  FROM ERMORA_TEST
 WHERE ROW_NUMBER BETWEEN (30 - 5) AND (30 + 5)

--Copy of output is shown below

25 ROW 25 DESCRIPTION
26 ROW 26 DESCRIPTION
27 ROW 27 DESCRIPTION
28 ROW 28 DESCRIPTION
29 ROW 29 DESCRIPTION
30 ROW 30 DESCRIPTION
31 ROW 31 DESCRIPTION
32 ROW 32 DESCRIPTION
33 ROW 33 DESCRIPTION
34 ROW 34 DESCRIPTION
35 ROW 35 DESCRIPTION

DROP TABLE ERMORA_TEST

Regards

T
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question Question
Seconds to minutes
Replies
5
Views
2K
Chris Miller
Chris Miller
dgillz
  • Locked
  • Question Question
How to convert varchar to time?
Replies
1
Views
1K
Chris Miller
Chris Miller
V
  • Locked
  • Question Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
1K
Volvere
V
Cpreston
  • Locked
  • Question Question
How to add a Letter to datepart
Replies
5
Views
1K
Andrzejek
Andrzejek
peac3
  • Locked
  • Question Question
convert hierarchy structure to parent children pair please...
2
Replies
22
Views
3K
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top