We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
Weather Observation Station 5
Weather Observation Station 5
Sort by
recency
|
6339 Discussions
|
Please Login in order to post a comment
WITH ShortestCity AS ( SELECT CITY, LENGTH(CITY) AS LEN, ROW_NUMBER() OVER (ORDER BY LENGTH(CITY) ASC, CITY ASC) AS t1 FROM STATION ), LongestCity AS ( SELECT CITY, LENGTH(CITY) AS LEN, ROW_NUMBER() OVER (ORDER BY LENGTH(CITY) DESC, CITY ASC) AS t2 FROM STATION ) SELECT CITY, LEN FROM ShortestCity WHERE t1 = 1
UNION ALL
SELECT CITY, LEN FROM LongestCity WHERE t2 = 1;
TRY THIS ONE
(SELECT CITY, LENGTH(CITY) AS length FROM STATION WHERE LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION) ORDER BY CITY ASC limit 1) UNION (SELECT CITY, LENGTH(CITY) AS length FROM STATION WHERE LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION) ORDER BY CITY ASC)
Solution for ORACLE:
select city, length from (select top 1 city,len(city) length, rank() over(order by len(city) asc, city asc) rnk from station union select top 1 city, len(city) length , rank() over(order by len(city) desc) rnk from station) as T order by len(city) asc
Hi, Anyone please check this query
SELECT CITY, LENGTH(CITY) AS length FROM STATION WHERE LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION) OR LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION) ORDER BY CITY ASC;