SQL Selecting "Window" Around Particular Row

Question

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 3x3 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.

Any thoughts?

Thanks,

Dom

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.

Answer 1

This is standard "Row ordering" problem... If your database has rowId capability you can use that, otherwise you need a subquery that counts the numnber of rows with Ids less than the id of the current row... like this:

-- asssuming @Id is value of id in the "middle"

 Select *  From Photos P
 Where (Select Count(*) From Photos
         Where id <= P.Id)
     Between (Select Count(*) From Photos
              Where id < @Id) - 4
        And  (Select Count(*) From Photos
              Where id < @Id) + 4

As a comment raised the album issue you would want to add album predicate to each subquery

   Select *  From Photos P
   Where (Select Count(*) From Photos
          Where album = @album
            And id <= P.Id)
     Between (Select Case When Count(*) < 4 
                      Then 4 Else Count(*) End
              From Photos
              Where album = @album
                 And id < @Id) - 4
        And  (Select Case When Count(*) < 4 
                      Then 4 Else Count(*) End
              From Photos
              Where album = @album
                  And id < @Id) + 4

Answer 2

If you are using SQL Server, you can use the row_number() function to give you the row order index and do something like this:

declare @selected_photo integer;
set @selected_photo = 5;

declare @buffer_size integer;
set @buffer_size = 2;

select
   ph.rownum,
   ph.id
from
   (select row_number() over (order by Id) as rownum, * from Photos) as ph
where
   ph.rownum between case
                         when @selected_photo - @buffer_size < 1 then 1
                         else @selected_photo - @buffer_size
                      end
                      and @selected_photo + @buffer_size

Edit: Here is an article on simulating the row_number() function in MySQL, combining that with this might get you what you need - I'd try it but don't have a MySQL db handy to play with at work. :-)

http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/

Ron

Answer 3

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):

(SELECT *
FROM photos
WHERE ID < #current_id#
ORDER BY ID DESC
LIMIT 10)
UNION
(SELECT *
FROM photos
WHERE ID >= #current_id#
ORDER BY ID ASC
LIMIT 10)
ORDER BY ID ASC

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.