jasongog jasongog - 6 months ago 25
SQL Question

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.

Answer

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
);