SQL command not properly ended in fetch and union using-CodePudding

Why this query return 'SQL command not properly ended' error?

select column1, length(column1) from test10 order by length(column1), column1 asc 
fetch first 1 rows only
union
select column1, length(column1) from test10 order by length(column1) desc, column1 asc fetch first 1 rows only;

CodePudding user response：

The database has problems to identify where what belongs.

so you can help the parser with parenthesis

CREATE tABLE test10 (column1 varchar2(10))

(select column1, length(column1) 
from test10 
order by length(column1), column1 asc 
fetch first 1 rows only)
union
(select column1, length(column1) 
from test10 
order by length(column1) desc, column1 asc 
fetch first 1 rows only);

COLUMN1 | LENGTH(COLUMN1)
:------ | --------------:

db<>fiddle here

hackerrank has a really old engine

So the result to that problem is

SELECT CITY, LENGTH(CITY)
FROM
(SELECT CITY, LENGTH(CITY) 
, Row_Number() OVER (ORDER BY  LENGTH(CITY) ASC,CITY ASC) rn
FROM STATION)
WHERE rn = 1
UNION
SELECT CITY, LENGTH(CITY)
FROM
(SELECT CITY, LENGTH(CITY) 
, Row_Number() OVER (ORDER BY  LENGTH(CITY) DESC,CITY DESC) rn
FROM STATION)
WHERE rn = 1
;