Skip navigation links

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


Advanced Search

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
select previous and next row Gabriel Quagliano 02/14/2007 11:11PM
Re: select previous and next row Bob Field 02/14/2007 11:37PM
Re: select previous and next row Gabriel Quagliano 02/14/2007 11:51PM
Re: select previous and next row Ffoeg No 01/01/2008 06:45AM
Re: select previous and next row Frode (se) 11/24/2007 10:33PM
Re: select previous and next row erhan baris 03/21/2008 03:55AM


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.