Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: Conditionally selecting next and previous rows

Tags:

mysql

http://thedailywtf.com/Articles/The-Hot-Room.aspx

You see how at the bottom there're links to the next and previous articles ("Unprepared For Divide_By_Zero" and "A Completely Different Game")? How do I do that, but selecting the next and previous non-private articles? This works for selecting the next article:

SELECT * FROM articles WHERE id > ? AND private IS NULL

But I cannot find a way to select the previous article.

What is the proper/efficient way to do this, preferably in one query?

like image 678
moo Avatar asked Oct 03 '08 23:10

moo


3 Answers

Or extending Jeremy's answer...
In one query

(SELECT * FROM articles WHERE id > ? 
 AND private IS NULL 
 ORDER BY id ASC LIMIT 1) 
UNION 
(SELECT * FROM articles WHERE id < ? 
 AND private IS NULL 
 ORDER BY id DESC LIMIT 1)
like image 55
mike Avatar answered Nov 02 '22 03:11

mike


Here's how I would do it:

-- next
SELECT * FROM articles WHERE id > ? AND private IS NULL ORDER BY id ASC LIMIT 1

-- previous
SELECT * FROM articles WHERE id < ? AND private IS NULL ORDER BY id DESC LIMIT 1

I'm not sure how to do it in one query. The only thing I can think of is possibly getting both the article you're displaying and the next article in one query, but that might be too confusing.

like image 5
Paige Ruten Avatar answered Nov 02 '22 05:11

Paige Ruten


How about a nested select?

SELECT * FROM articles WHERE id IN (
    SELECT id FROM articles WHERE id > ? AND private IS NULL ORDER BY id ASC LIMIT 1)
)
OR id IN (
    SELECT id FROM articles WHERE id < ? AND private IS NULL ORDER BY id DESC LIMIT 1
);
like image 2
Max Stewart Avatar answered Nov 02 '22 04:11

Max Stewart