List the nearest items to the record pulled

[Windsailor]

Hello,

I would like to list items (both above and below) in *sequence* from the original item returned.

Lets say I returned a document with the number of 8100; I want to be able to pull / get the nearest items to this document and list them in order... for instance:


Item displayed:
8100

Items returned:
8101
7998
8105
7992
7990
8150



I thought about using a UNION / TOP statement; with one query going up and the other going down... but I am not sure it would display correctly... what would be the best way to do this?

To make a sample structure:

TableDocument
DocumentID
DocumentName

Thanks,

 

[cjard]

Sadly, SQLServer doesnt support lag/lead so I'd do:

SELECT * FROM (
SELECT TOP 5 * FROM tabledocument WHERE documentID < 8100 ORDER BY documentid DESC
UNION ALL
SELECT TOP 6 * FROM tabledocument WHERE documentID >= 8100 ORDER BY documentid ASC
) t
ORDER BY
  documentid ASC --or whatever you want to order by