Select a row and rows around it

mysql, sql

Solution

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

Problem

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 ```

Original source