Fetch two next and two previous entries in a single SQL query

Question

Hi,

I want to display an image gallery, and on the view page, one should be able to have a look at a bunch of thumbnails: the current picture, wrapped with the two previous entries and the two next ones.

The problem of fetching two next/prev is that I can't (unless I'm mistaken) select something like MAX(id) WHERE idxx.

Any idea?

note: of course the ids do not follow as they should be the result of multiple WHERE instances.

Thanks Marshall

Answer 1

If you are just interested in the previous and next records by id couldn't you just have a where clause that restricts WHERE id=xx, xx-1, xx-1, xx+1, xx+2 using multiple WHERE clauses or using WHERE IN ?

Answer 2

if your IDs are continuous you could do

where id >= @id-2 and id <= @id+2

Otherwise I think you'd have to union 3 queries, one to get the record with the given id and two others messing about with top and order by like this

select *
from table
where id = @id

union

select top 2 *
from table
where id < @id
order by id desc

union

select top 2 *
from table
where id > @id
order by id

Performance will not be too bad as you aren't retrieving massive sets of data but it won't be great due to using a union.

If you find performance starts being a problem you could add columns to hold the ids of the previous and next items; calculating the ids using a trigger or overnight process or something. This will mean you only do the hard query once rather than each time you need it.

Answer 3

You'll have to forgive the SQL Server style variable names, I don't remember how MySQL does variable naming.

SELECT *
  FROM photos
 WHERE photo_id = @current_photo_id
 UNION ALL
SELECT *
  FROM photos
 WHERE photo_id > @current_photo_id
 ORDER BY photo_id ASC
 LIMIT 2
 UNION ALL
SELECT *
  FROM photos
 WHERE photo_id < @current_photo_id
 ORDER BY photo_id DESC
 LIMIT 2;

This query assumes that you might have non-contiguous IDs. It could become problematic in the long run, though, if you have a lot of photos in your table since TOP is often evaluated after the entire result set has been retrieved from the database. YMMV.

In a high load scenario, I would probably use these queries, but I would also prematerialize them on a regular basis so that each photo had a PreviousPhotoOne, PreviousPhotoTwo, etc column. It's a bit more maintenance, but it works well when you have a lot of static data and need performance.

Answer 4

I think this method should work fine for non-continguous ID's and should be more effecient than using a UNION's. currentID would be set either using a constant in SQL or passing from your program.

SELECT * FROM photos WHERE ID = currentID OR ID IN (
    SELECT ID FROM photos WHERE ID < currentID ORDER BY ID DESC LIMIT 2
) OR ID IN (
    SELECT ID FROM photos WHERE ID > currentID ORDER BY ID ASC LIMIT 2
) ORDER BY ID ASC