Placing a WHERE statement separately for a UNION function to work

user_6003740 0
Viewed 35

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

Table

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;
window-functions union sql-order-by oracle sql
2 Answers

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.

user_90979060

I would recommend row_number() instead of rownum. Using it, you don't need union, you can just do:

select *
from (
    select s.*, 
        row_number() over(order by length(city), city) rn_asc,
        row_number() over(order by length(city) desc, city) rn_desc
    from station s
) s
where 1 in (rn_asc, rn_desc)

If you were to do this with union (which is less efficient than the above solution), then a row-limiting clause is more appropriate (available starting Oracle 12c):

(
    select * 
    from station 
    order by length(city), city 
    fetch first 1 row only
) union all (
    select * 
    from station 
    order by length(city) desc, city 
    fetch first row only
)
user_106767160
Related