SQL query for finding the longest name and shortes

Question

I have a table with one of the columns is of type varchar(city). and want to find the longest and shortest of values stored in that column.

select a.city, a.city_length from (select city, char_length(city) city_length 
from station order by city, city_length) a
where a.city_length = (select min(a.city_length) from a) or
      a.city_length = (select max(a.city_length) from a)
group by a.city_length;

Can anyone help? Thanks

---

One solution:

select * from (select city, char_length(city) city_length from station order by city, city_length) a group by a.city_length order by a.city_length limit 1;
select * from (select city, char_length(city) city_length from station order by city, city_length) a group by a.city_length order by a.city_length desc limit 1;

Answer 1

For oracle SQL in one resulting table. This will retrieve the min and max city names, and if same length will get the first city sorted in alphabetic order.

SELECT * FROM (
    SELECT CITY, LENGTH(CITY) CITY_LENGTH 
    FROM STATION 
    ORDER BY CITY_LENGTH ASC, CITY ASC ) MAX_LEN  
WHERE ROWNUM <= 1
UNION
SELECT * FROM (
    SELECT CITY, LENGTH(CITY) CITY_LENGTH 
    FROM STATION 
    ORDER BY  CITY_LENGTH DESC, CITY ASC ) MIN_LENGTH  
WHERE ROWNUM <= 1;

Answer 2

with cte (rank, city , CityLength) 
As 
(select  dense_rank() over (partition by len(city) order by city asc) as Rank, city, len(city) 
    from station 
where len(city) in 
    ((select max(len(city)) from station) 
    union (select min(len(city)) from station)))
select city,citylength from cte where rank = 1;

Answer 3

Shortest:

select TOP 1 CITY,LEN(CITY) LengthOfCity FROM STATION ORDER BY LengthOfCity ASC, CITY ASC;

Longest:

select TOP 1 CITY,LEN(CITY) LengthOfCity FROM STATION ORDER BY LengthOfCity DESC, CITY ASC;

This works for HackerRank challenge problem (MS SQL Server).

Answer 4

Maybe a simpler option since I imagine you are looking for help with a solution to a Hacker Rank question? The addition of limits made it simpler for me to debug where the issue was with the returned error.

SELECT city, length(city) FROM station order by length(city) desc limit 1;

SELECT city, length(city) FROM station order by length(city) asc, city asc limit 1

Answer 5

Shortest:

select city, char_length(city) city_length from station order by city_length, city limit 1;

Longest:

select city, char_length(city) city_length from station order by city_length desc, city limit 1;

Answer 6

I don’t think that we need to use Min and Max functions and Group by is also not required.

We can achieve this using the below code:

select top 1 City, LEN(City) City_Length from STATION order by City_Length ASC,City ASC

select top 1 CITY, LEN(city) City_Length from station order by City_Length desc, City ASC

but in this case, it will display output in 2 table and if we would like to combine in a single table then we can use Union or Union ALL. Below is the SQL query for the same

  select * from (
     select top 1 City, LEN(City) City_Length from STATION order by City_Length ASC,City ASC) TblMin
   UNION
   select * from (
   select top 1 CITY, LEN(city) City_Length from STATION order by City_Length desc, City ASC) TblMax

here I am nesting the select statement inside a subquery because when we are using order by clause then we cannot use Union or Union ALL directly that is why I have written it inside a subquery.