[Solved] Why can’t we use UNION ALL and ORDER BY together in SQL

by


ORDER BY, when using a UNION ALL goes at the end of the entire statement, not at the end of each one. You also have a statement terminator (;) after your first query, meaning you have a SELECT TOP(1) query followed by a further query that starts with UNION ALL.

You need to use subqueries here and then UNION those:

SELECT *
FROM (SELECT TOP (1) LEN(CITY) AS CITYLEN, CITY
      FROM STATION
      ORDER BY LEN(CITY),CITY) S
UNION ALL
SELECT *
FROM (SELECT TOP (1) LEN(CITY) AS CITYLEN, CITY
      FROM STATION
      ORDER BY LEN(CITY) DESC,CITY DESC) S

solved Why can’t we use UNION ALL and ORDER BY together in SQL