Select Rows around selected Row

[Laffern]

I have a problem with a SQL Statement. For example : I have 10 Rows in the database with the ID 1 to 10.

The main row is selected by date. Then 3 other rows should be selected. 1 row above the main one and 2 below the main row, and they aren't selected by date. The "TOP" syntax doesn't work really here and the question is, if there is a possibility to realize this in one SELECT statement only.

 

[hmckillop]

You will need to give a sample of your 10 rows and then what expected output. No idea how to give syntax from your explanation.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Laffern]

Example Rows

Columns : ID, Group, Date

1 - G1 - 30.04.2005
2 - G2 - 29.04.2005
3 - G1 - 28.04.2005
4 - G1 - 27.04.2005
5 - G2 - 26.04.2005
6 - G3 - 25.04.2005
7 - G2 - 24.04.2005
8 - G2 - 23.04.2005
9 - G4 - 22.04.2005
10 - G5 - 21.04.2005

Main row is ID 6 (Today).

I need this output :

5 - G2 - 26.04.2005
6 - G3 - 25.04.2005
7 - G2 - 24.04.2005
8 - G2 - 23.04.2005

 

[hmckillop]

Are the ids always sequential, if so why not use the id row as the parameter.
e.g.

declare @paramid int
set @paramid = 6
select * from mytable where id >= @paramid -1 and id <= @paramid + 2
order by id

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Laffern]

not sequential ... that's the problem

 

[vongrunt]

One (ugly) way:

declare @paramID int; set @paramID = 6

select * from
(	select top 1 ID, [Group], [Date]
	from myTable
	where ID < @paramID
	order by ID desc
) A
UNION
select top 3 ID, [Group], [Date]
from myTable
where ID >= @paramID
order by ID asc

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba