SQL query to find the longest name and shortest name in a table

Question

SQL query to find the longest name and shortest name in a table

I have a table with one of the columns of type varchar (city). and want to find the longest and shortest of the values stored in this 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;

+12

sql

Michael xu Feb 14 '16 at 20:11

source share

22 answers

Banketeshvar narayan · Answer 1 · 2016-10-16T17:30:38+0000

I do not think that we need to use the Min and Max functions and Group by is also not required.

We can achieve this using the code below:

 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 the output in 2 table , and if we want to combine into one table, 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 insert the select expression inside the subquery, because when we use the order by clause, we cannot use Union or Union ALL directly, so I wrote it inside the subquery.

mrusom · Answer 2 · 2017-08-13T12:29:22+0000

Shortest:

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

Series:

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

This works for a problem with calling HackerRank (MS SQL Server).

Richkenny · Answer 3 · 2017-04-25T14:18:29+0000

Maybe a simpler option, since I believe that you are looking for help in resolving the issue of a hacker pack? Adding limits made it easier for me to debug where the problem 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

Gordon linoff · Answer 4 · 2016-02-14T20:16:16+0000

A request requires only a few settings. The main problem is that you cannot use a in the subquery how you do it:

 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);

However, an easier way to write a 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);

Pydata · Answer 5 · 2018-04-24T15:44:37+0000

In MySQL

 (select city, LENGTH(city) cityLength from station order by cityLength desc,city asc LIMIT 1) union all (select city, LENGTH(city) cityLength from station order by cityLength asc,city asc LIMIT 1)

Shnugo · Answer 6 · 2016-02-14T20:34:36+0000

This is an approach with CTE. First he finds the longest and shortest than the corresponding cities:

 DECLARE @tbl TABLE(CityName VARCHAR(100)); INSERT INTO @tbl VALUES ('xy'),('Long name'),('very long name'),('middle'),('extremely long name'); WITH MyCTE AS ( SELECT MAX(LEN(CityName)) AS Longest ,MIN(LEN(CityName)) AS Shortest FROM @tbl ) SELECT * FROM MyCTE --You must think about the chance of more than one city matching the given length CROSS APPLY(SELECT TOP 1 CityName FROM @tbl WHERE LEN(CityName)=Longest) AS LongestCity(LongName) CROSS APPLY(SELECT TOP 1 CityName FROM @tbl WHERE LEN(CityName)=Shortest) AS ShortestCity(ShortName)

Result

 Longest Shortest LongName ShortName 19 2 extremely long name xy

helpMeLearn · Answer 7 · 2016-05-28T22:36:50+0000

I did this in SQL Server using CTE and the dense_rank function. How does the rating work?

The first section (groups of forms) in length, i.e. the same lengths form a group (section). Then arrange all the names in alphabetical order in each section. Then assign ranks (dRank columns) in each section. Thus, rank 1 in each group will be assigned to names that are displayed in alphabetical order first in the corresponding section. All this happens in a common table expression (cte block)

 "with cte as ( select *, LEN(city) as length, DENSE_RANK() over (partition by len(city) order by city) as dRank from Station )" select city,length from cte where dRank = 1 and length = (select MIN(length) from cte) UNION select city,length from cte where dRank = 1 and length = (select max(length) from cte)"

umang gala · Answer 8 · 2016-09-02T20:03:41+0000

Initially finding the shortest city length and combining with the longest city length. This minimizes the complexity of the request.

 (select city, char_length(city) as len_city from station order by len_city limit 1) union ( select city, char_length(city) as len_city from station order by len_city desc limit 1) order by len_city

RioRicoRick · Answer 9 · 2016-09-24T19:35:27+0000

 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

Alex l · Answer 10 · 2017-03-20T04:26:37+0000

In Oracle:

  select * from (select city, min (length (city)) minl from station group by city order by minl, city) where rownum = 1;
 select * from (select city, max (length (city)) maxl from station group by city order by maxl desc, city) where rownum = 1;

nepete · Answer 11 · 2017-08-31T08:10:52+0000

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;

MT0 · Answer 12 · 2018-01-12T14:56:48+0000

In Oracle (and any other language that supports analytic functions), using the analytic function ROW_NUMBER , you can assign rows to a unique number according to the ASC completed (or DESC completed) city length. Since there can be several lines with the same length, then the secondary order can be applied to get the first city of this length in alphabetical order. Then you need an external query to filter the results only for 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 cities with the shortest (or longest) name, 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

Kaushik nayak · Answer 13 · 2018-01-12T16:34:26+0000

In Oracle 12c, this can be done using FETCH..FIRST

Shortest

 select * FROM station ORDER BY LENGTH(city) DESC FETCH FIRST 1 ROWS ONLY;

Series

 select * FROM station ORDER BY LENGTH(city) ASC FETCH FIRST 1 ROWS ONLY;

ishan kankane · Answer 14 · 2018-02-09T20:04:37+0000

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);

JEEVAN GEORGE ANTONY · Answer 15 · 2018-05-12T09:23:07+0000

The following query seems simple enough:

 select city, leng from (select top 1 city, len(city) leng from station where len(city) = (select min(len(city)) from station) order by city Union all select top 1 city, len(city) leng from station where len(city) = (select max(len(city)) from station) order by city) result;

The 1st query inside returns the city with the minimum length, and the 2nd query returns the city with the maximum length.

Hope this helps.

Anksg · Answer 16 · 2018-10-13T19:38:19+0000

For the shortest city name:

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

For the longest city name:

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

Vinoth karthick · Answer 17 · 2019-06-17T17:29:23+0000

In Oracle, that would be

 SELECT CITY,LENGTH(CITY) FROM (SELECT MIN(CITY) CITY FROM STATION WHERE LENGTH(CITY)=(SELECT MIN(LENGTH(CITY)) FROM STATION)) UNION ALL SELECT CITY,LENGTH(CITY) FROM (SELECT MAX(CITY) CITY FROM STATION WHERE LENGTH(CITY)=(SELECT MAX(LENGTH(CITY)) FROM STATION));

Sohaib anwaar · Answer 18 · 2019-07-26T05:44:05+0000

This request will work well in your conditions, these 2 requests are exactly the same. In the first request, we simply sort the record in descending order to take the name of the city with the longest length, and in the second part we simply record in ascending order to take the city with the shortest length, all other queries are the same. Kidney, look.

Detail:

Select statement to clear notes
Use group by because I use the Len () aggregation function.

Ordering all found records in descending and ascending order to get the top 1 and take the city of maximum and minimum length.

 select Top 1 City,max(len(City)) as Length from STATION group by City ORDER BY Length desc select Top 1 City,max(len(City)) as Length from STATION group by City ORDER BY Length ASC

Nitesh · Answer 19 · 2016-11-01T09:35:28+0000

 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;

user3115408 · Answer 20 · 2017-04-19T16:31:24+0000

 select * from (select city,length(city)from station group by city having length(city) in ((select min(length(city))from station))order by city) where rownum<2 UNION select * from (select city,length(city)from station group by city having length(city) in ((select max(length(city))from station))order by city) where rownum<2;

Thuat nguyen · Answer 21 · 2017-06-29T04:26:01+0000

Shortest:

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

Series:

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

Darshit raj · Answer 22 · 2017-05-10T18:25:04+0000

I think this should work:

  SELECT MAX(CITY) , LENGTH(MAX(CITY)) FROM STATION; SELECT MIN(CITY) , LENGTH(MIN(CITY)) FROM STATION;

SQL query to find the longest name and shortest name in a table

More articles: