MySQL Forums :: MySQL Query Browser :: select previous and next row

I searched around for ways of selecting previous and next in a generic manner and found this thread but no answer.
Create a subtable with a custom column containing row number and generated dynamically depending how we should select previous or next.
Some databases (i.e. RDBMS) have built-in row numbering ROW_NUMBER() function but we can emulate this in MySQL.

// Select previous in sequence
SET @num = 0;

SELECT
id,
nota,
author,
category
FROM ( SELECT
@num := @num + 1 AS number,
id,
nota,
author,
category
FROM
table
WHERE
author='yo' AND category='noticias'
ORDER BY nota ASC) AS tbl
WHERE
number < :current_number
ORDER BY number DESC
LIMIT 1;

// select next in sequence
SET @num = 0;

SELECT
id,
nota,
author,
category
FROM ( SELECT
@num := @num + 1 AS number,
id,
nota,
author,
category
FROM
table
WHERE
author='yo' AND category='noticias'
ORDER BY nota ASC) AS tbl
WHERE
number > :current_number
ORDER BY number ASC
LIMIT 1;



Edited 1 time(s). Last edit at 11/24/2007 10:35PM by Frode (se).