It's quite possible a question like this has been asked before, but I can't think of the terms to search for.
I'm working on a photo gallery application, and want to display 9 thumbnails showing the context of the current photo being shown (in a 3×3 grid with the current photo in the centre, unless the current photo is in the first 4 photos being shown, in which case if e.g. if the current photo is the 2nd I want to select photos 1 through 9). For example, given an album containing the list of photos with ids:
1, 5, 9, 12, 13, 18, 19, 20, 21, 22, 23, 25, 26
If the current photo is 19, I want to also view:
9, 12, 13, 18, 19, 20, 21, 22, 23
If the current photo is 5, I want to also view:
1, 5, 9, 12, 13, 18, 19, 20, 21
I've been thinking of something along the lines of:
SELECT * FROM photos WHERE ABS(id - currentphoto) < 5 ORDER BY id ASC LIMIT 25
but this doesn't work in the case where the ids are non-sequential (as in the example above), or for the case where there are insufficient photos before the currentphoto.
p.s. Please leave a comment if anything is unclear, and I'll clarify the question. If anyone can think of a more useful title to help other people find this question in future, then please comment too.
Probably could just use a UNION, and then trim off the extra results in the procedural code that displays the results (as this will return 20 rows in the non-edge cases):
EDIT: Increased limit to 10 on both sides of the UNION, as suggested by le dorfier.
EDIT 2: Modified to better reflect final implementation, as suggested by Dominic.