Q:

Finding max length of a string + which string it is in Oracle SQL

This for HackerRank Weather Observation 5 problem on databases (https://www.hackerrank.com/challenges/weather-observation-station-5). How would I solve this?

Query the two cities in STATION with the shortest and longest CITY
  names, as well as their respective lengths (i.e.: number of characters
  in the name). If there is more than one smallest or largest city,
  choose the one that comes first when ordered alphabetically.

This is what I have so far
SELECT CITY, MAX LENGTH(CITY) FROM STATION;

But it obviously doesn't work.

A:

Here is a solution with window functions:
select city, length(city)
from
(
  select 
    city, 
    row_number() over (order by length(city), city) as shortest_is_one,
    row_number() over (order by length(city) desc, city) as longest_is_one
  from station
)
where shortest_is_one = 1 or longest_is_one = 1;

An alternative would be a UNION query with FETCH FIRST 1 ROW ONLY:
(
  select city, length(city) 
  from station
  order by length(city), city
  fetch first 1 row only
)
union
(
  select city, length(city) 
  from station
  order by length(city) desc, city
  fetch first 1 row only
);

And here is still another query, again using UNION:
select city, length(city)
from
(
  select max(city) keep (dense_rank first order by length(city), city) as city
  from station
  union
  select max(city) keep (dense_rank first order by length(city) desc, city) as city
  from station
);

