MySQL Forums
Forum List  »  MySQL Query Browser

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).

Options: ReplyQuote


Subject
Written By
Posted
February 14, 2007 11:37PM
January 01, 2008 06:45AM
Re: select previous and next row
November 24, 2007 10:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.