mysql - Select a row and rows around it

Question

Ok, let's say I have a table with photos.

What I want to do is on a page display the photo based on the id in the URI. Bellow the photo I want to have 10 thumbnails of nearby photos and the current photo should be in the middle of the thumbnails.

Here's my query so far (this is just an example, I used 7 as id):

SELECT
    A.*
FROM
  (SELECT
       *
   FROM media
   WHERE id < 7
   ORDER BY id DESC
   LIMIT 0, 4
   UNION
   SELECT
       *
   FROM media
   WHERE id >= 7
   ORDER BY id ASC
   LIMIT 0, 6
  ) as A
ORDER BY A.id

But I get this error:

#1221 - Incorrect usage of UNION and ORDER BY

Answer 1

Only one ORDER BY clause can be defined for a UNION'd query. It doesn't matter if you use UNION or UNION ALL. MySQL does support the LIMIT clause on portions of a UNION'd query, but it's relatively useless without the ability to define the order.

MySQL also lacks ranking functions, which you need to deal with gaps in the data (missing due to entries being deleted). The only alternative is to use an incrementing variable in the SELECT statement:

SELECT t.id, 
       @rownum := @rownum+1 as rownum 
  FROM MEDIA t, (SELECT @rownum := 0) r

Now we can get a consecutively numbered list of the rows, so we can use:

WHERE rownum BETWEEN @midpoint - ROUND(@midpoint/2) 
                 AND @midpoint - ROUND(@midpoint/2) +@upperlimit

Using 7 as the value for @midpoint, @midpoint - ROUND(@midpoint/2) returns a value of 4. To get 10 rows in total, set the @upperlimit value to 10. Here's the full query:

SELECT x.* 
  FROM (SELECT t.id, 
               @rownum := @rownum+1 as rownum 
          FROM MEDIA t, 
               (SELECT @rownum := 0) r) x
 WHERE x.rownum BETWEEN @midpoint - ROUND(@midpoint/2) AND @midpoint - ROUND(@midpoint/2) + @upperlimit

But if you still want to use LIMIT, you can use:

  SELECT x.* 
    FROM (SELECT t.id, 
                 @rownum := @rownum+1 as rownum 
            FROM MEDIA t, 
                 (SELECT @rownum := 0) r) x
   WHERE x.rownum >= @midpoint - ROUND(@midpoint/2)
ORDER BY x.id ASC
   LIMIT 10