Syntax of UNION in MySQL-CodePudding

While running the following query


SELECT *
FROM
    (SELECT CITY, LENGTH(CITY)
    FROM STATION
    ORDER BY LENGTH(CITY), CITY) AS T1
LIMIT 1
UNION
SELECT *
FROM
    (SELECT CITY, LENGTH(CITY)
    FROM STATION
    ORDER BY LENGTH(CITY) DESC, CITY) AS T2
LIMIT 1;

I get

ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION

I can't understand what is the error here

CodePudding user response：

Quoting the docs,

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT *
FROM
    (SELECT CITY, LENGTH(CITY)
    FROM STATION
    ORDER BY LENGTH(CITY), CITY) AS T1
LIMIT 1)
UNION
(SELECT *
FROM
    (SELECT CITY, LENGTH(CITY)
    FROM STATION
    ORDER BY LENGTH(CITY) DESC, CITY) AS T2
LIMIT 1)

CodePudding user response：

It could be that you are using ORDER BY in both parts of union (SQL Server does not allow this). Try removing and / or ordering the final result set