Geek asks and answers

Select the line and the lines around it.

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

I want to make the page display a photo based on the identifier 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 is my request 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
+4
source share
7 answers

For query UNION'd, only one sentence can be specified ORDER BY. It doesn't matter if you use UNIONor UNION ALL. MySQL supports the sentence LIMITin parts of the query UNION'd, but it is relatively useless without the ability to determine the order.

MySQL , ( - ). - SELECT:

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

, :

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

7 @midpoint, @midpoint - ROUND(@midpoint/2) 4. 10 , @upperlimit 10. :

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

LIMIT, :

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

, :

SELECT  A.* FROM  (
   (
      SELECT  *  FROM gossips
      WHERE id < 7
      ORDER BY id DESC
      LIMIT 2
   )
  UNION
   (
      SELECT * FROM gossips
      WHERE id > 7
      ORDER BY id ASC
      LIMIT 2
   )

 ) as A
ORDER BY A.id
+4

, "" UNION. - :

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

, malonso (+1), id = 1, 5 . , . 10 , :

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

, select top ( sql server). :

Oracle:

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

MySQL:

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

, , .

, , 10 . , 90 , id = 88... , . MySQL - :

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

, , .

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;
+1

. , , .

+1

, , , ( ).

OMG Ponies, rownum :

set @id = 7;
SELECT natSorted.id 
  FROM (
       SELECT gravitySorted.* FROM (
           SELECT Media.id, IF(id <= @id, @gravity := @gravity + 1, @gravity := @gravity - 1) AS gravity 
             FROM Media, (SELECT @gravity := 0) g
       ) AS gravitySorted ORDER BY gravity DESC LIMIT 10
  ) natSorted ORDER BY id;

, :

NOTE. In the example below, I made a table with 20 rows and deleted identifiers 6 and 9 to ensure that the space in the identifiers does not affect the results

First, we assign each row a gravity value that is centered around the particular row you are looking for (in this case, when id is 7). The closer the line is to the desired line, the higher the value will be:

SET @id = 7;
SELECT Media.id, IF(id <= @id, @gravity := @gravity + 1, @gravity := @gravity - 1) AS gravity 
  FROM Media, (SELECT @gravity := 0) g

returns:

+----+---------+
| id | gravity |
+----+---------+
|  1 |       1 |
|  2 |       2 |
|  3 |       3 |
|  4 |       4 |
|  5 |       5 |
|  7 |       6 |
|  8 |       5 |
| 10 |       4 |
| 11 |       3 |
| 12 |       2 |
| 13 |       1 |
| 14 |       0 |
| 15 |      -1 |
| 16 |      -2 |
| 17 |      -3 |
| 18 |      -4 |
| 19 |      -5 |
| 20 |      -6 |
| 21 |      -7 |
+----+---------+

Then we order all the results according to the value of gravity and the limit on the desired number of rows:

SET @id = 7;
SELECT gravitySorted.* FROM (
    SELECT Media.id, IF(id <= @id, @gravity := @gravity + 1, @gravity := @gravity - 1) AS gravity 
      FROM Media, (SELECT @gravity := 0) g
) AS gravitySorted ORDER BY gravity DESC LIMIT 10

returns:

    +----+---------+
    | id | gravity |
    +----+---------+
    |  7 |       6 |
    |  5 |       5 |
    |  8 |       5 |
    |  4 |       4 |
    | 10 |       4 |
    |  3 |       3 |
    | 11 |       3 |
    |  2 |       2 |
    | 12 |       2 |
    |  1 |       1 |
    +----+---------+

At this point, we have all the desired identifiers, we just need to sort them in the original order:

set @id = 7;
SELECT natSorted.id 
  FROM (
       SELECT gravitySorted.* FROM (
           SELECT Media.id, IF(id <= @id, @gravity := @gravity + 1, @gravity := @gravity - 1) AS gravity 
             FROM Media, (SELECT @gravity := 0) g
       ) AS gravitySorted ORDER BY gravity DESC LIMIT 10
  ) natSorted ORDER BY id;

returns:

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  7 |
|  8 |
| 10 |
| 11 |
| 12 |
+----+
0
source

More articles:


All Articles