Placing a WHERE statement separately for a UNION function to work

Question

I am working on a hackerrank exercise where I have to find the city with the shortest name and with the longest name and if there are many with the same amount of characters in the name then take the first one in alphabetical order

This is the table that was provided

and after many tries, I managed to create this code that works

SELECT * FROM (
SELECT CITY, LENGTH(CITY) AS LENGTH_CITY FROM STATION ORDER BY LENGTH_CITY ASC, CITY) WHERE ROWNUM = 1
UNION
SELECT * FROM (
SELECT CITY, LENGTH(CITY) AS LENGTH_CITY FROM STATION ORDER BY LENGTH_CITY DESC, CITY) WHERE ROWNUM = 1;

So my question is, originally I had made this other piece of code that does not work, and I was wondering why do I have to place the "WHERE ROWNUM = 1" separately in a SELECT* for the UNION to work?

SELECT CITY, LENGTH(CITY) AS LENGTH_CITY FROM STATION WHERE ROWNUM = 1 ORDER BY LENGTH_CITY ASC, CITY
UNION
SELECT CITY, LENGTH(CITY) AS LENGTH_CITY FROM STATION WHERE ROWNUM = 1 ORDER BY LENGTH_CITY DESC, CITY;

Answer 1

I was wondering why do I have to place the "WHERE ROWNUM = 1" separately in a SELECT* for the UNION to work?

It has nothing to do with UNION, but with ORDER BY.

The 2nd query you posted certainly doesn't do what you intended because WHERE clause executes before ORDER BY, so this:

select city
from station
where rownum = 1
order by city

says: give me the first random row from the table and then sort that row by city.

Therefore: first sort rows, then take the first one:

select *
from (select city
      from station
      order by city
     )
where rownum = 1

Apply that to your situation, lengths and stuff.