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

You query requires just a few tweaks. The fundamental problem is that you cannot use a in the subquery as you are doing:

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

That said, a simpler way to write the query is:

select s.*
from station s cross join
     (select min(char_length(city)) as mincl, max(char_length(city)) as maxcl
      from station
     ) ss
where char_length(s.city) in (mincl, maxcl);

Answer 2

For oracle :

select min(city),length(city) from station where length(city) <= all(select 
length(city) from station) group by length(city);

select max(city),length(city) from station where length(city) >= all(select 
length(city) from station) group by length(city);

Answer 3

Ascending:

SELECT city, CHAR_LENGTH(city) FROM station ORDER BY CHAR_LENGTH(city), city LIMIT 1;

Descending:

SELECT city, CHAR_LENGTH(city) FROM station ORDER BY CHAR_LENGTH(city) DESC, city LIMIT 1;

Answer 4

select top(1) city, max(len(city)) [Length] from station group by city order by [Length]
select top(1) city, max(len(city)) [Length] from station group by city order by [Length] DESC

Tested in SQL Server 2016

Answer 5

In Oracle (and any other language that supports analytic functions), using the ROW_NUMBER analytic function you can assign the rows a unique number according to ASCending (or DESCending) length of the city. Since there may be multiple rows with the same length then a secondary order can be applied to get the first city of that length alphabetically. Then all you need is an outer query to filter the results to only the shortest (or longest) name:

SELECT city
FROM   (
  SELECT CITY,
         ROW_NUMBER() OVER ( ORDER BY LENGTH( CITY ) ASC,  CITY ) shortest_rn,
         ROW_NUMBER() OVER ( ORDER BY LENGTH( CITY ) DESC, CITY ) longest_rn
  FROM   station
)
WHERE shortest_rn = 1
OR    longest_rn  = 1;

If you want to return all the cities with the shortest (or longest) name then use DENSE_RANK instead of ROW_NUMBER:

SELECT city
FROM   (
  SELECT CITY,
         DENSE_RANK() OVER ( ORDER BY LENGTH( CITY ) ASC  ) shortest_rn,
         DENSE_RANK() OVER ( ORDER BY LENGTH( CITY ) DESC ) longest_rn
  FROM   station
)
WHERE shortest_rn = 1
OR    longest_rn  = 1
ORDER BY shortest_rn, city; -- Shortest first and order tied lengths alphabetically

Answer 6

For shortest name of city :

SELECT ST.CITY,LENGTH(ST.CITY) AS LENGTH FROM STATION ST
ORDER BY LENGTH ASC, ST.CITY ASC
LIMIT 1;

For longest name of city :

SELECT ST.CITY,LENGTH(ST.CITY) AS LENGTH FROM STATION ST
ORDER BY LENGTH DESC, ST.CITY DESC
LIMIT 1;