Weather Observation Station 5

Sort by

recency

|

7124 Discussions

|

  • + 0 comments

    WITH cte AS ( SELECT city, LENGTH(city) AS city_length, ROW_NUMBER() OVER (ORDER BY LENGTH(city), city) AS rank_min, ROW_NUMBER() OVER (ORDER BY LENGTH(city) DESC, city DESC) AS rank_max FROM station ) SELECT city, city_length FROM cte WHERE rank_min = 1 UNION SELECT city, city_length FROM cte WHERE rank_max = 1;

  • + 0 comments

    with cte as( select city , length(city), row_number() over(order by length(city), city) rnk_min, row_number() over(order by length(city) desc, city desc) rnk_max from station order by length(city), city) select city, length(city) from cte where rnk_min = 1 or rnk_max = 1

  • + 0 comments

    with cte as( select city , length(city) , row_number() over(partition by length(city) order by length(city), city) rnk from station order by length(city), city) select city, length(city)from cte where rnk = 1 limit 1;

    with cte as( select city , length(city) , row_number() over(partition by length(city) order by length(city), city) rnk from station order by length(city), city) select city, length(city) from cte where rnk = 1 limit 1 offset 16;

  • + 0 comments

    Crazy way in one select:

    SELECT CITY, LENGTH(CITY) AS LEN FROM ( SELECT CITY, LENGTH(CITY), ROW_NUMBER() OVER (ORDER BY LENGTH(CITY), CITY) AS rn_min, ROW_NUMBER() OVER (ORDER BY LENGTH(CITY) DESC, CITY DESC) AS rn_max FROM STATION ) sub WHERE rn_min = 1 OR rn_max = 1 order by city;

  • + 0 comments

    SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY),CITY LIMIT 1; SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC,CITY LIMIT 1;