I wish to display a picture gallery, as well as on the vista page, you ought to have the ability to take a look at a lot of pictures: the present picture, wrapped using the two previous records and also the two next ones.

The issue of fetching two next/prev is the fact that I can not (unless of course I am mistaken) choose something similar to MAX(id) WHERE idxx.

Any idea?

note: obviously the ids don't follow as they must be caused by multiple WHERE instances.

Thanks Marshall

You will need to forgive the SQL Server style variable names, I do not 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 question assumes which you may have non-contiguous IDs. It might become problematic over time, though, for those who have lots of photos inside your table since TOP is frequently examined after the whole result set continues to be retrieved in the database. YMMV.

Inside a high load scenario, I'd most likely begin using these queries, however i would also prematerialize them regularly to ensure that each photo were built with a PreviousPhotoOne, PreviousPhotoTwo, etc column. It's a little more maintenance, however it is effective if you have lots of static data and want performance.

in case your IDs are continuous you could do this

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

Otherwise I believe you'd need to union 3 queries, one to obtain the record using the given id and 2 others playing about with top and order by such as 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 won't be bad while you aren't locating massive teams of data however it will not do well because of utilizing a union.

If you discover performance begins as being a problem you could include posts to carry the ids from the previous and then products calculating the ids utilizing a trigger or overnight process or something like that. This can mean you simply perform the hard query once instead of every time you require it.

I believe this process should work acceptable for non-continguous ID's and really should become more effecient than utilizing a UNION's. currentID could be set either utilizing a constant in SQL or passing out of 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

If you're just interested in the earlier and then records by id could not you've just got a where clause that limits WHERE id=xx, xx-1, xx-1, xx+1, xx+2 using multiple WHERE clauses or using Whereby ?