SELECT SQL query help

Question

I'm trying to figure out a little bit unusual select query.

First of all, what I have is an id of a single row in the table. I want to select a number of rows from the table (let's the say 10) with the single row with the id I know being in the table being in the middle of the rowset. So it will select let's say 4 rows before the id I know and 5 rows after the id (or 5, 4).

In case the id is in the beginning of the table or at the end if the table it still selects 10 rows, so for example if the id is the second row in the table it will select 1 row before it and 8 rows after it. When the id is the last row it will select 9 rows before it (plus the one with the id, of course).

I'm not sure I'm being clear enough but I'm having trouble explaining this in a better way.

EDIT:

Here's more information. Imagine table like this (simplified):

id    title
1     Title 1
2     Title 2
3     Title 3
4     Title 4
5     Title 5
6     Title 6
7     Title 7
8     Title 8
9     Title 9
10    Title 10
11    Title 11
12    Title 12
13    Title 13
14    Title 14
15    Title 15
16    Title 16
17    Title 17
18    Title 18
19    Title 19
20    Title 20
21    Title 21
22    Title 22
23    Title 23
24    Title 24
25    Title 25

Now let's say the id I have is 11, then the query will select these rows:

7, 8, 9, 10, **11**, 12, 13, 14, 15, 16

Let's say the id is 2, the query will select:

1, **2**, 3, 4, 5, 6, 7, 8, 9, 10

Let's say the id is 25, the query will select:

16, 17, 18, 19, 20, 21, 22, 23, 24, **25**

And so on. I hope it's clearer now.

Answer 1

declare @p_id numeric, @p_range numeric
select @p_id = 4 -- known ID
, @p_range = 10 --range
select * from myTable
where id between (@p_id - (@p_range / 2)) and (@p_id + (@p_range / 2))
order by id

That will select 10 records around a known ID (4), 5 before, 5 after.

You'll have to do some more manipulation if you want to have exactly 10 records, and work out how many exist before and after (in the case of id = 2, etc)

Answer 2

I'm not a MySQL guy, but maybe this will work if the IDs are sequential and unbroken:

select ID, your_other_columns from (
  select
    :yourID-ID+0.5 as priority,
    * from T where ID between :yourID-9 and :yourID
    union all
  select
    ID-:yourID as priority,
    * from T where ID between :yourID and :yourID+9
  where ID >= :yourID and :yourID >= ID-9
) as T20 order by priority limit 10;

Answer 3

Single query... a bit ugly ;)

SET @target := 11
(SELECT *
        FROM (
                (SELECT * FROM your_table WHERE id <= @target ORDER BY id desc LIMIT 10)
                        UNION
                (SELECT * FROM your_table WHERE id > @target ORDER BY id asc LIMIT 9)
        ) AS u ORDER BY ABS(CAST(u.id AS signed integer) - @target) ASC LIMIT 10
) ORDER BY id;