5

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?

moo
  • 7,619
  • 9
  • 42
  • 40

4 Answers4

9

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)
ConroyP
  • 40,958
  • 16
  • 80
  • 86
mike
  • 5,047
  • 2
  • 26
  • 32
  • 2
    This one performs weird when you're at the first/last record of your selection. It returns just one record, but I can't find a way to figure out which. – Fuzzy76 Aug 08 '10 at 17:06
  • If the current article ID is 10 and the query returns only one result, here's what you can do: - if the ID is lower than 10, the article is older - if the ID is higher than 10, the article is newer If it is still not solving the problem, you can extend the query and add some meaningful column, such as `select 'newer' as when, * from articles ...` – mike Aug 09 '10 at 07:34
5

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.

Paige Ruten
  • 172,675
  • 36
  • 177
  • 197
2

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
);
Max Stewart
  • 3,573
  • 26
  • 28
2

You can get away with subselects etc in your particular case, but if you need anything more complicated (for example: given an initial balance and a list of payments and chargebacks, calculate account balance at every point of time) you probably would want to write a stored procedure that uses SQL REPEAT/WHILE/LOOP clauses and allows use of variables and so on.

mst
  • 247
  • 1
  • 5