Error on execution of T-SQL UNION statement. Any explanation?

user_12272047 0
Viewed 65

On executing a T-SQL UNION statement in SQL Server, I get the following error:

Msg 156, Level 15, State 1, Server WIN-ILO9GLLB9J0, Line 7
Incorrect syntax near the keyword 'UNION'

I am a student practicing SQL queries. I have to find the name of a city with maximum characters and the name of a city with the minimum characters from a table. In case there are cities with the same character length I have to print the first city in alphabetical order. I have written the following code:

SELECT TOP 1 CITY, LEN(CITY) 
FROM STATION 
ORDER BY LEN(CITY), CITY

UNION

SELECT TOP 1 CITY, LEN(CITY) 
FROM STATION 
ORDER BY LEN(CITY) DESC, CITY

The two queries work fine on their own.

union sql-server sql
4 Answers

Here is one way to make your current top-union syntax work, using a subquery:

SELECT CITY, CITY_LEN
FROM
(
    SELECT TOP 1 CITY, LEN(CITY) AS CITY_LEN FROM STATION ORDER BY LEN(CITY), CITY
    UNION
    SELECT TOP 1 CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY) DESC, CITY
) t;

However, we could just as easily use ROW_NUMBER here:

WITH cte AS (
    SELECT CITY, LEN(CITY) AS LEN_CITY,
        ROW_NUMBER() OVER (ORDER BY LEN(CITY), CITY) rn1,
        ROW_NUMBER() OVER (ORDER BY LEN(CITY) DESC, CITY) rn2
    FROM STATION
)

SELECT CITY, LEN_CITY
FROM cte
WHERE 1 IN (rn1, rn2);

Demo

user_18632290

This might be a clearer way to approach the problem:

select * 
from station 
where len(city) in (
  select max(len(city)) from station
  union
  select min(len(city)) from station
)
order by len(city), city

You can have an order by in a subquery if that query uses top, but you can't have a pattern of:

select * from x order by a
union 
select * from y order by a

Though you're allowed:

select * from x
union 
select * from y 
order by a

The order by in this case is done after the union and orders the whole unioned set, as if it were this:

select * from (
  select * from x
  union 
  select * from y 
) z
order by a

There isn't a concept of doing the first select from x, ordering it, doing the select from y, ordering it, then unioning the two sets of ordered results together unless you specifically use parentheses to turn them into sub queries(and even then only if they use TOP) as per some of the other answers. As presented in the form of "x union y" there must be only one order by statement, at the end of the sql and it is applied to the whole set. A reason why sql server is fussy about this "cannot have an order by in the middle of a statement" (oracle isn't) is that the order of rows coming out of a [union] operation isn't guaranteed so it doesn't make sense to order them on the way in - it's a waste of time and resource. The general rule is "if you want your rows in a certain order it should be the last thing you do" and hence any interim steps of ordering are a waste. You could argue that this use case should be allowed and I'd find it a valid argument- equally valid as, say, "top without order by should not be allowed yet it is". If you dug into it/asked the sql team at ms you'd probably find that the job of parsing an sql is hard enough and they stopped at "no order by in the middle of a statement" without adding another rule of "unless it's a top operation"

user_14106640

There is a good answer with windows function but since you are a beginner you can use sub query to UNION, initially the error is because you can not mention an explicit ORDER BY when you try to union but using sub query you can overcome this.

SELECT * 
FROM
    (SELECT TOP 1 CITY, LEN(CITY) LEN 
     FROM STATION 
     ORDER BY LEN(CITY), CITY) a

UNION

SELECT * 
FROM
    (SELECT TOP 1 CITY, LEN(CITY) LEN 
     FROM STATION 
     ORDER BY LEN(CITY) DESC, CITY) a
user_85900730

This should work:

SELECT * FROM (select TOP 1 ... here)  AS q1
UNION ALL
SELECT * FROM (select TOP 1 ... here) AS q2

It's todo with top 1 ... order by and UNION, I suppose obviously.

user_5810760
Related