How to retrieve neighbour records from SQLServer

How to retrieve neighbour records from SQLServer

Post by WP » Tue, 20 Feb 2007 03:53:09


I have a table in SQLServer, with a candidate index on a character field,
and I would like to retrieve 5 records before and 5 records after a given
record which is obtained by key. In a native VFP table, this would be easy
making a SEEK and then SKIP -1 and SKIP +1 for five times. But how to do it
in SQLServer? The table has 2 miilion records, so I cannot download them
into a cursor to later make SKIP. One of my thoughts was to make two
SELECTS:

SELECT TOP 5 * FROM table where keyfield<mykey ORDER BY keyfield
DESCENDING;
UNION ALL;
SELECT TOP 5 * FROM table where keyfield>mykey ORDER BY keyfield ASCENDING

Is there a faster way, using only one SELECT ?

Walter
 
 
 

How to retrieve neighbour records from SQLServer

Post by Anders Alt » Tue, 20 Feb 2007 20:23:54

Hi Walter,
Using VFP9 or SQL Server this seems to work:

CREATE CURSOR xx (name char(1))
for i = 1 to 26
INSERT INTO xx values (chr(64+i))
NEXT

SELECT * FROM xx WHERE xx.name<='J' AND 3< ;
(SELECT COUNT(*) FROM xx as xx2 ;
WHERE xx2.name<xx.name ) ;
union ALL ;
SELECT * FROM xx WHERE xx.name>'J' AND 10 < ;
(SELECT COUNT(*) FROM xx as xx2 ;
WHERE xx2.name>xx.name )

Result: F to O. Five > 'J' + five > 'J'
Duplicates will be included and add to the number of rows returned.
-Anders



it
ASCENDING