Search code examples
mysqlsqlunionmysql-error-1064

Need help understanding MySql syntax

I am stuck on this hackerrank problem for MySql and keep receiving a syntax error. I am trying to select the cities with the shortest and longest names that comes alphabetically.

When I try with order by city limit 1 on both of them I receive an error. When I only order by city limit 1 after the second query I only receive a response from the first query.

(select CITY, length(CITY) from STATION order by length(CITY) limit 5) order by city limit 1; 
UNION 
(select CITY, length(CITY) from STATION order by length(CITY) DESC limit 5) order by city limit 1;

This is the error I receive 

ERROR 1064 (42000) at line 1: 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 
(select CITY, length(CITY) from STATION order by length(CITY) DESC limit ' at line 2
Solution

  • You have 4 order bys where only two are necessary. 

    (select CITY, length(CITY)
 from STATION
 order by length(CITY)
 limit 1
) union all
(select CITY, length(CITY)
 from STATION
 order by length(CITY) DESC
 limit 1
);

    It is unclear why you would want limit 5 in the subquery, but then limit 1 outside it.

    Another problem with your query is that you have a semicolon after the first subquery, in effect splitting the compound query into two -- and union is not a valid keyword for starting a query.