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
To select rows using selection symbols for character or graphic data, use the LIKE keyword in a WHERE clause, and the underscore and percent sign as selection symbols. You can create multiple row conditions, and use the AND, OR, or IN keywords to connect the conditions.
Select one or more rows and columns Or click on any cell in the column and then press Ctrl + Space. Select the row number to select the entire row. Or click on any cell in the row and then press Shift + Space. To select non-adjacent rows or columns, hold Ctrl and select the row or column numbers.
To select more than one row in the data view, click one row, then hold the Control (Windows) or Command (Mac) key and select each of the other rows you wish to edit or remove. To select a continuous list, click one row, then hold the Shift key and click the last row. Was this helpful?
To select a larger range, it's easier to click the first cell and hold down the Shift key while you click the last cell in the range.
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
I resolve this by using the below code:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With