SQL query for finding the longest name and shortest name in a table

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

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.