Unable to figure out...

[ermora]

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

 

[Thargy]

Ermora,

I've shown how it's done below. The example uses +/- 5 and only has 100 records, just to save time.

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

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