'Query the two cities in STATION with the shortest and longest CITY names, [closed]

Query: Query the 2 cities contained in STATION table 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.

enter image description here

where LAT_N is the northern latitude and LONG_W is the western longitude.

Sample Input :

Let's say that CITY only has four entries: DEF, ABC, PQRS and WXY

Sample Output:

ABC 3
PQRS 4
sqloracle


Solution 1:[1]

TRY THIS :)

mysql code.... simple one

select CITY,LENGTH(CITY) from STATION order by Length(CITY) asc, CITY limit 1; 
select CITY,LENGTH(CITY) from STATION order by Length(CITY) desc, CITY limit 1;

Edit:

The above solution is not working for me as it doesn't sort alphabetically. As commented by @omotto the following is the proper way to make it work. I have tried in SQL server and it works.

select top 1 city, len(city) from station order by len(city) ASC, city ASC; 
select top 1 city, len(city) from station order by len(city) DESC, city ASC;

Solution 2:[2]

For MS SQL Server:

Declare @Small int
Declare @Large int
select @Small = Min(Len(City)) from Station 
select @Large = Max(Len(City)) from Station
select Top 1 City as SmallestCityName,Len(City) as Minimumlength from Station where Len(City) = @Small Order by City Asc
select Top 1 City as LargestCityName,Len(City) as MaximumLength from Station where Len(City) = @Large Order by City Asc

For Oracle server:

select * from(select distinct city,length(city) from station order by length(city) asc,city asc) where rownum=1 union
select * from(select distinct city,length(city) from station order by length(city) desc,city desc) where rownum=1;

Solution 3:[3]

( select CITY, 
       char_length(CITY) as len_city 
  from STATION 
  where char_length(CITY)=(select char_length(CITY) 
                          from STATION 
                          order by char_length(CITY) LIMIT 1) 
  Order by CITY LIMIT 1) 
 UNION ALL 
 (select CITY,
        char_length(CITY) as len_city 
  from STATION 
  where char_length(CITY)=(select char_length(CITY) 
                           from STATION 
                           order by char_length(CITY) DESC LIMIT 1)  
  Order by CITY DESC LIMIT 1) 
  ORDER BY char_length(CITY);

Solution 4:[4]

select min(city), len
  from (
        select city, length(city) len,
               max(length(city)) over() maxlen,
               min(length(city)) over() minlen
          from station
       )
 where len in(minlen,maxlen)
 group by len

Subquery gets the list of cities and it's length. At the same time "window functions" min/max over() get minimal and maximal length for all rows in set (table). Main query filter only cities of length is min/max. min(city) with the group by len gives the result first name on the alphabetical order.

Solution 5:[5]

Here is another way to do it using the always handy row_number analytic function:

with cte as (
  select city,
         length(city) as len,
         row_number() over (order by length(city), city) as smallest_rn,
         row_number() over (order by length(city) desc, city) as largest_rn
    from station
)
select city, len
  from cte
 where smallest_rn = 1
union all
select city, len
  from cte
 where largest_rn = 1

Solution 6:[6]

  SELECT * FROM (SELECT city, length(city) 
  FROM station
  WHERE LENGTH(city)=(SELECT MIN(LENGTH(city)) FROM station) ORDER BY city ) 
  WHERE ROWNUM =1;

  SELECT city, LENGTH(city) 
  FROM station  
  WHERE LENGTH(city)=(SELECT MIN(LENGTH(city)) FROM STATION) 
  AND ROWNUM=1
  ORDER BY CITY;

Solution 7:[7]

Try this one using UNION:

SELECT MIN(city), LENGTH(city)
FROM Station
WHERE LENGTH(city) =
(SELECT MIN(LENGTH(city))
FROM Station)
UNION
SELECT MIN(city), LENGTH(city)
FROM Station
WHERE LENGTH(city) =
(SELECT MAX(LENGTH(city))
FROM Station)

Of course, my assumption is that your table name is Station and column name is City. See the related post below about only choosing the first record alphabetically:

Return only the first alphabetical result of SELECT

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Aamir Shahzad
Solution 2 radhikesh93
Solution 3 Jaydip Jadhav
Solution 4 Community
Solution 5 sstan
Solution 6 sonique
Solution 7 Community

Related Questions