'shortest and longest city name

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.

May i please know answer to this question?

  SELECT CITY,
         LENGTH(CITY)
    FROM STATION
ORDER BY CITY ASC
   FETCH FIRST 1 ROWS ONLY ;

why is this query not working for fetch shortest length city?

sqloracle


Solution 1:[1]

I would use ROW_NUMBER here:

WITH cte AS (
    SELECT s.*, ROW_NUMBER() OVER (ORDER BY LENGTH(CITY), CITY) rn_first,
        ROW_NUMBER() OVER (ORDER BY LENGTH(CITY) DESC, CITY) rn_last
    FROM STATION s
)

SELECT CITY, LENGTH(CITY) AS CITY_LENGTH
FROM cte
WHERE 1 IN (rn_first, rn_last)
ORDER BY LENGTH(CITY);

This would generate a two record result set, with the shortest city name appearing first, and the longest, last.

Solution 2:[2]

My favorite way to do this in Oracle uses the keep keyword:

select min(city) keep (dense_rank first order by length(city) asc) as shortest_city,
       min(city) keep (dense_rank first order by length(city) desc) as longest_city,
       min(length(city)) as min_len,
       max(length(city)) as max_len
from station;

In my experience, keep has very good performance, so I wouldn't be surprised if this had the best performance on large data.

Solution 3:[3]

Thank you for the responses.I will check for "keep" once. I solved it this way.Is there any other possibility to optimise it or make it more simpler?

**

select city,length(city) as "SL"

from (select city from station  order by city asc) 

where length(city) in (select min(length(city)) from station) and rownum=1; 

select city,length(city) as "LL"

from (select city from station  order by city asc) 

where length(city) in (select max(length(city)) from station) 

order by city desc;`

**

Solution 4:[4]

For MS SQL Server, Solution will be as follow.

Declare @Small int
Declare @Large int
select @Small = Min(Len(CITY )) from STATION 
select @Large = Max(Len(CITY )) from STATION
;with cte1 as(
select Top 1 CITY  as SmallestCity,Len(CITY ) as Minimumlength from STATION  where Len(CITY ) = @Small Order by CITY  Asc
),
cte2 as(
select Top 1 CITY  as LargestCity,Len(CITY ) as MaximumLength from STATION  where Len(CITY ) = @Large Order by CITY  Asc 
)
select * from cte1
union
select * from cte2

Its out put look like this, Out put of query

Solution 5:[5]

The code I have written works for Microsoft SQL Server

select city, len(city) from station where len(city) = (select max(len(city)) from station); 

select city, len(city) from station where len(city) = (select min(len(city)) from station) order by city desc offset 2 rows

Solution 6:[6]

(select city, length(city) from station order by length(city), city Limit 1)
union all
(select city, length(city) from station order by length(city) DESC, city Limit 1);

Solution 7:[7]

You need to modify your ORDER BY:

SELECT CITY, LENGTH(CITY)
FROM STATION
ORDER BY LENGTH(CITY) DESC, CITY
FETCH FIRST 1 ROWS ONLY ;

This will give the answer for the longest name. For the shortest name, just change the ORDER BY to use ASC for LENGTH(CITY).

Solution 8:[8]

SELECT CITY,
       LENGTH(CITY) as length
  FROM CITY
 ORDER BY length ASC limit 1;

Solution 9:[9]

SELECT CITY,LENGTH(CITY)

FROM STATION

WHERE LENGTH(CITY) IN (SELECT min(LENGTH(CITY))

                                   FROM  STATION
                            )

AND ROWNUM <= 1

UNION

SELECT CITY,LENGTH(CITY)

FROM STATION WHERE LENGTH(CITY) IN (SELECT max(LENGTH(CITY))

                               FROM  STATION )

AND ROWNUM <= 1

ORDER BY CITY ASC;

Solution 10:[10]

SELECT top 1 City,LEN(City) as Citylength into #tmp
FROM STATION
WHERE LEN(City)=(SELECT MIN(LEN(City)) FROM STATION )
order by City asc

SELECT top 1 City,LEN(City) as Citylength into #tmp1
FROM STATION
WHERE LEN(City)=(SELECT MAX(LEN(City)) FROM STATION )
order by City

select * from #tmp
union
select * from #tmp1

Related Questions