Re: select previous and next row
Posted by:
Frode (se)
Date: November 24, 2007 10:33PM
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).