Geek Answers Handbook

Request two cities in STATION with the shortest and longest CITY names,

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

Input Example:

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

Output Example:

 ABC 3 PQRS 4
+6
source share
7 answers

TRY IT:)

mysql code .... simple

 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;
+3
source 
 ( 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);
+3
source 
 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

The subquery gets a list of cities and length. At the same time, the "window functions" min/max over() get the minimum and maximum length for all rows in the set (table). The main filter of the request are only cities with min / max lengths. min(city) with the len group gives the name of the result in alphabetical order.

+2
source

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;
+2
source

Here is another way to do this, using the always convenient 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
+1
source 
 SELECT TOP 1 CITY,LEN(CITY) from STATION Where LEN(CITY)=(Select min(len(CITY)) from STATION ) order by CITY SELECT TOP 1 CITY,LEN(CITY) from STATION Where LEN(CITY)=(Select MAX(len(CITY)) from STATION )
+1
source

Try using this with 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 guess is that the name of your table is the name of the station and column is City. See the related message below about selecting only the first entry in alphabetical order:

Return only the first alphabetical SELECT result

0
source

More articles:


All Articles