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

I don't believe that you can have an "order by" in different sections of a UNION. Could you just do something like this:

SELECT * FROM media where id >= 7 - 4 and id <= 7 + 4 ORDER BY id

Answer 2

I'm agree with the answer suggested by malonso(+1), but if you try it with id= 1, you will get only 5 thumbnails. I don't know if you want this behaviour. If you want always 10 thumbs, you can try:

select top 10 * from media where id > 7 - 4

The problem is that select top is database dependent (in this case is a sql server clause). Other database has similar clauses:

Oracle:

SELECT *  media
FROM media
WHERE ROWNUM < 10
AND id > 7 - 4

MySQL:

SELECT * 
FROM media
WHERE id > 7 - 4
LIMIT 10

So maybe you can use the last one.

If we do it, we will have the same problem if you want the last 10 thumbs. By example, If we have 90 thumbs and we give an id=88 ... You can solve it adding an OR condition. In MySQL will be something like:

SELECT * 
    FROM media
    WHERE id > 7 - 4
    OR (Id+5) > (select COUNT(1) from media)
    LIMIT 10

Answer 3

If you're happy to use temp tables, your original query could be broken down to use them.

SELECT
    *
FROM media
WHERE id < 7
ORDER BY id DESC
LIMIT 0, 4
INTO TEMP t1;

INSERT INTO t1
SELECT
   *
FROM media
WHERE id >= 7
ORDER BY id ASC
LIMIT 0, 6;

select * from t1 order by id;
drop table t1;

Answer 4

Try union all instead. Union requires the server to ensure that the results are unique and this conflicts with your ordering.

Answer 5

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