MYSQL query to get 'n' rows nearby given row

Question

I have a MySQL table by name 'videos', where one of the column is 'cat' (INT) and 'id' is the PRIMARY KEY.

So, if 'x' is the row number,and 'n' is the category id, I need to get nearby 15 rows

Case 1: There are many rows in the category before and after 'x'.. Just get 7 each rows before and after 'x'

SELECT * FROM videos WHERE cat=n AND id<x ORDER BY id DESC LIMIT 0,7
SELECT * FROM videos WHERE cat=n AND id>x LIMIT 0,7

Case 2: If 'x' is in the beginning/end of the the table -> Print all (suppose 'y' rows) the rows before/after 'x' and later print 15-y rows after/before 'x'

Case 1 is not a problem but I am stuck with Case 2. Is there any generic method to get 'p' rows nearby a row 'x' ??

Answer 1

This query will always position N (exact id match) at the centre of the data, unless there are no more rows (in either direction), in which case rows will be added from the prior/next sections as required, while still preserving data from prior/next (as much as available).

set @n := 28;

SELECT * FROM
(
SELECT * FROM
(
    (SELECT v.*, 0 as prox  FROM videos v WHERE cat=1 AND id = @n)
    union all
    (SELECT v.*, @rn1:=@rn1+1 FROM (select @rn1:=0) x, videos v WHERE cat=1 AND id < @n ORDER BY id DESC LIMIT 15)
    union all
    (SELECT v.*, @rn2:=@rn2+1 FROM (select @rn2:=0) y, videos v WHERE cat=1 AND id > @n ORDER BY id LIMIT 15)
) z
ORDER BY prox
LIMIT 15
) w
order by id

For example, if you had 30 ids for cat=1, and you were looking at item #28, it will show items 16 through 30, #28 is the 3rd row from the bottom.

Some explanation:

SELECT v.*, 0 as prox FROM videos v WHERE cat=1 AND id = @n

• v.* means to select all columns in the table/alias v. In this case, v is the alias for the table videos.
• 0 as prox means to create a column named prox, and it will contain just the value 0

The next query:
SELECT v.*, @rn1:=@rn1+1 FROM (select @rn1:=0) x, videos v WHERE cat=1 AND id < @n ORDER BY id DESC LIMIT 15

• v.* - as above
• @rn1:=@rn1+1 uses a variable to return a sequence number for each record in this subquery. It starts with 1 and for each record, following the ORDER BY id DESC, it will be numbered 2, then 3 etc.
• (select @rn1:=0) x This creates a subquery aliased as x, all it does is ensures the variable @rn1 starts with the value 1 for the first row.

The end result is that the variable and 0 as prox ranks each row based on how close it is to the value @n. The clause order by prox limit 15 takes the 15 that are closest to N.