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;

On which RDBMS are you working? It's important for us to know, as we would be able to use more efficient rdbms-specific features (and give you some hints about advanced tecniques) — Insac, Nov 01 '16 at 09:58
Note that there might not be 'the' longest name, meaning you might have more than one. — YoYo, May 10 '17 at 18:37

Banketeshvar Narayan · Answer 1 · 2016-10-16T17:47:29.460

43

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.

edited Oct 16 '16 at 17:47

answered Oct 16 '16 at 17:30

Banketeshvar Narayan

3,799
4
38
46

1

with oracle error popped as `ERROR at line 1: ORA-00904: "TOP": invalid identifier` – Hara Aug 27 '17 at 04:25
2

Product specific answer to a question with no dbms specified. At least tell us which dbms this is for. – jarlh Jun 11 '18 at 09:08
@jarlh It's for SQL Server. – Ashish Patel Nov 04 '18 at 10:46

score 20 · Answer 2 · edited Aug 18 '17 at 14:48

20

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

edited Aug 18 '17 at 14:48

Cody Gray - on strike

239,200
50
490
574

answered Aug 13 '17 at 12:29

mrusom

271
2
6

if we choose `Oracle`, then this query does not work in hacker rank. it throws error as ` error is **ERROR at line 1:** ORA-00904: "TOP": invalid identifier` – Hara Aug 27 '17 at 04:23
This query is for MS SQL Server and won't work with oracle. Try this for oracle: Shortest: select * from (select CITY,LENGTH(CITY) LengthOfCity FROM STATION ORDER BY LengthOfCity ASC, CITY ASC) where rownum = 1; Longest: select * from (select CITY,LENGTH(CITY) LengthOfCity FROM STATION ORDER BY LengthOfCity DESC, CITY ASC) where rownum = 1; – mrusom Aug 28 '17 at 05:43
3

For MySql: SELECT CITY, LENGTH(CITY) as LEN FROM STATION ORDER BY LEN ASC, CITY ASC LIMIT 1; SELECT CITY, LENGTH(CITY) as LEN FROM STATION ORDER BY LEN DESC, CITY ASC LIMIT 1; – stormwild Aug 31 '17 at 11:25
what is LengthOfCity ... is it similer like alias ... there is no column name as LengthOfCity on hacker rank – Shubham Jain Jan 22 '18 at 09:42
@ShubhamJain you're right, it's alias, defined to use it later in ORDER. – ogostos Mar 10 '18 at 11:23
@ogostos So the shorthand is to leave out 'AS'? I've been learning SQL, and not seeing the 'AS' made this query very confusing. – Microscone Oct 03 '18 at 20:01
Great for my use - with both MySQL and PostgreSQL (i.e. using limit and mulitple arguments for the 'order by' clause) – nate Jun 03 '20 at 15:21

score 13 · Answer 3 · answered Apr 24 '18 at 15:44

13

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)

answered Apr 24 '18 at 15:44

PyData

146
1
3

why LIMIT 1 ? Can you please shed some light? – Taylor Jul 01 '19 at 17:15
1

@Taylor, the `LIMIT 1` reduces the output of the query to one entry (in this case city). With the query you get the city with the shortest and longest city name. – Alex_P Aug 04 '19 at 21:33

RichKenny · Answer 4 · 2017-04-25T15:56:19.407

8

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

edited Apr 25 '17 at 15:56

answered Apr 25 '17 at 14:18

RichKenny

179
2
5

score 4 · Answer 5 · answered Feb 14 '16 at 20:16

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

score 2 · Answer 6 · answered Mar 20 '17 at 04:26

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;

score 2 · Answer 7 · answered Aug 31 '17 at 08:10

2

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;

answered Aug 31 '17 at 08:10

nepete

911
5
6

score 2 · Answer 8 · answered Jan 30 '21 at 08:05

2

This is another way of doing it in MySQL. May not be the best, but still be an option that is logically correct.

select
   city,
   length(city) 
from
   station 
where
   length(city) in 
   (
      select
         max(length(city)) 
      from
         station 
      union
      select
         min(length(city)) 
      from
         station
   )
order by
   length(city) desc,
   city asc limit 2;

answered Jan 30 '21 at 08:05

1218985

7,531
2
25
31

IF anyone is looking for a Hackerank solution, I can verify this works as of June 2022 – fatfrog Jun 29 '22 at 14:36

score 1 · Answer 9 · answered Feb 14 '16 at 20:34

This is an approach with a CTE. First it finds the longest and shortest, than the matching 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)

The result

Longest Shortest    LongName               ShortName
19       2          extremely long name    xy

score 1 · Answer 10 · edited Aug 18 '17 at 14:49

I did this in SQL Server using CTE and dense_rank function. How the ranking works?

First partition (form groups) over the lengths, i.e same lengths make a group (partition). Then order all the names alphabetically within each partition. Then assign ranks (dRank column) within each partition. So rank 1s in each group will be assigned to names which alphabetically appear first in their respective partition. All this happens in the 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)"

score 1 · Answer 11 · edited Sep 24 '16 at 19:35

1

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

edited Sep 24 '16 at 19:35

Petter Friberg

21,252
9
60
109

answered Sep 24 '16 at 19:35

Cozmo

53
3

score 1 · Answer 12 · edited Apr 02 '19 at 15:13

1

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;

edited Apr 02 '19 at 15:13

Kevin Hernández

704
10
25

answered Oct 13 '18 at 19:38

AnksG

488
4
9

score 1 · Answer 13 · answered Dec 11 '20 at 21:24

I used the WITH clause for Oracle to save the shortest /longest names in a temporary variable rather than querying inside the main "from" clause. SQL WITH clause example

WITH shortnames AS
(SELECT city, length(city) 
 FROM station 
 ORDER BY length(city) asc, city),

 longnames AS
 (SELECT city, length(city) 
  FROM station
  ORDER BY length(city) desc, city)

SELECT * FROM shortnames WHERE ROWNUM=1
UNION ALL
SELECT * FROM longnames WHERE ROWNUM=1;

score 0 · Answer 14 · edited Sep 02 '16 at 20:47

0

Initially finding the shortest length of the city and taking an union with the longest length of the city. This minimizes the complexity of the query.

(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

edited Sep 02 '16 at 20:47

dimodi

3,969
1
13
23

answered Sep 02 '16 at 20:03

umang gala

69
4

score 0 · Answer 15 · answered Jan 12 '18 at 14:56

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

score 0 · Answer 16 · answered Jan 12 '18 at 16:34

0

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

Shortest

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

Longest

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

answered Jan 12 '18 at 16:34

Kaushik Nayak

30,772
5
32
45

score 0 · Answer 17 · answered Feb 09 '18 at 20:04

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

score 0 · Answer 18 · edited May 13 '18 at 09:17

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, while the 2nd query returns the city with the maximum length.

Hope this helps.

score 0 · Answer 19 · answered Jun 17 '19 at 17:29

In Oracle it 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));

score 0 · Answer 20 · answered Jul 26 '19 at 05:44

This Query will work well in your condition these 2 query are exactly same. In the first query we just ordering the recording in descending order to take the city name with the highest length and in the second part we are just taking records in the ascesding order to take city with the minimum lenght all the rest query is same. Kidnly have a look.

Detail:

Select statement to take out records
Use group by clause because I am using Len() aggregating function.

Ordering all the retrieved records in the descending and ascending order to get the top 1 and take max and min length city.

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

score 0 · Answer 21 · answered Apr 28 '20 at 09:38

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;

score 0 · Answer 22 · answered May 24 '20 at 09:45

0

select city,length(city) from (select city,length(city),rank() over(partition by length(city) order by length(city),city asc) as rnk from station)a where a.rnk=1;

answered May 24 '20 at 09:45

kathiravan

11
1

score 0 · Answer 23 · answered Jun 12 '20 at 09:56

In Oracle, we can do like this

select city, length(city)
from (select city from STATION order by length(city) DESC, city ASC)
where rownum = 1 
union
select city, length(city)  
from (select city from STATION order by length(city), city ASC)
where rownum = 1;

The idea is to get the longest and shortest city then union them into one result.

score 0 · Answer 24 · answered Jun 16 '20 at 07:00

0

SELECT MAX(LENGTH(transaction_id)) AS Longest ,MIN(LENGTH(transaction_id)) AS Shortest FROM cards

answered Jun 16 '20 at 07:00

Vishal Kumar

4,419
1
25
31

score 0 · Answer 25 · answered Dec 04 '20 at 16:06

length(CITY) will return the length of the string,

https://www.w3schools.com/sql/func_mysql_length.asp

(select CITY, length(CITY) from STATION order by length(CITY),CITY limit 1)
UNION
(select CITY, length(CITY) from STATION order by length(CITY) DESC limit 1);

score 0 · Answer 26 · answered Aug 30 '21 at 08:26

For Oracle this worked for me(HackerRank)

--shortest 
SELECT CITY,L FROM 
(select CITY,length(CITY) L
from STATION   
order by length(CITY) ASC, CITY ASC)
WHERE ROWNUM=1
UNION ALL 
--longest 
SELECT CITY,L FROM 
(select CITY,length(CITY) L
from STATION   
order by length(CITY) DESC, CITY ASC)
WHERE ROWNUM=1;

Texto original I tried many ways, but this work, I ordered by length, and after alphabetically, and finally get first row only.

score 0 · Answer 27 · answered Jan 06 '22 at 07:56

This works for mysql

-- smallest
SELECT city, length(city) FROM station 
WHERE length(city) = (select min(length(city)) from station)
ORDER BY city
limit 1;

-- largest
SELECT city, length(city) FROM station 
WHERE length(city) = (select max(length(city)) from station)
ORDER BY city
limit 1;

score 0 · Answer 28 · answered Jan 14 '22 at 18:44

Here is a solution that strictly uses MIN and MAX

The concept here is to apply MIN to the length of the city name. Within those results, again apply MIN to city name itself to find the first alphabetically.

Of course, use MAX in the same way to find the maximum city length, and perform a UNION of the two queries for a final solution.

No sorts, ASC, DESC, LIMIT, or ORDER BY needed.

Great reference: https://www.zentut.com/sql-tutorial/sql-min-max/

(
    SELECT s3.CITY, s3.Len 
    FROM (
        SELECT CITY, s1.Len
        FROM (
            SELECT CITY, LENGTH(CITY) AS Len
            FROM STATION
        ) s1
        WHERE
            s1.Len = (
                SELECT MIN(s2.Len2)
                FROM (
                    SELECT LENGTH(CITY) AS Len2
                    FROM STATION
                ) s2
            )
        ) s3
    WHERE
        s3.CITY = (
            SELECT MIN(CITY)
            FROM STATION
            WHERE s3.Len = LENGTH(CITY)
        )
) UNION (
    SELECT s3.CITY, s3.Len 
    FROM (
        SELECT CITY, s1.Len
        FROM (
            SELECT CITY, LENGTH(CITY) AS Len
            FROM STATION
        ) s1
        WHERE
            s1.Len = (
                SELECT MAX(s2.Len2)
                FROM (
                    SELECT LENGTH(CITY) AS Len2
                    FROM STATION
                ) s2
            )
        ) s3
    WHERE
        s3.CITY = (
            SELECT MIN(CITY)
            FROM STATION
            WHERE s3.Len = LENGTH(CITY)
        )
);

score 0 · Answer 29 · answered Jan 30 '23 at 19:09

In addition to all answers above, I want to share a trick I used in T-SQL but exist in any version:

I calculate the length with leading 0 pads which allow alphabetic sort
- In this example, I assume all text will be less than 10MB, so 7 digit number at max
- REPLACE(STR(CAST(LEN(city) AS INT) ,7), SPACE(1), '0')
I append the 0-padded LEN() to the value
- concat( <step1> , city )
I choose MAX() or MIN()
- This works because the 0 padded number can be sorted alphabetically.
- Example: "099" < "100", but "99" > "100" because "9" > "1" in ascii)
I remove the number from the value
- STUFF( <step 3> ,1,7,'') as we replace 7 firts chars with '', and our number will always be 7 char long because of 0 padding

Finally, the following SQL will return both answers in the same query, without windows and without unions:

SELECT
    STUFF(min(
        concat(
            REPLACE(STR(CAST(LEN(city) AS INT) ,7), SPACE(1), '0')
            ,city)) 
        ,1,7,'')
        as [min_len],
    STUFF(max(
        concat(
            REPLACE(STR(CAST(LEN(city) AS INT) ,7), SPACE(1), '0')
            ,city)) 
        ,1,7,'')
        as [max_len]
from station

This also lets you get min max by any other info from any other column (in the same row), like "station_ranking" column if exists.

Here is a dbfiddle with MySQL example:

SELECT
    SUBSTR(min(
        concat(
            LPAD(length(city), 7, '0') 
            ,city)) 
        ,7+1)
        as min_name_len,
    SUBSTR(max(
        concat(
            LPAD(station_rank, 2, '0') 
            ,city)) 
        ,2+1)
        as max_name_rank
from stations

https://www.db-fiddle.com/f/76ew1fK9D5V3vopbGUuYxh/4

score -1 · Answer 30 · edited Nov 01 '16 at 09:45

-1

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;

edited Nov 01 '16 at 09:45

Toby Speight

27,591
48
66
103

answered Nov 01 '16 at 09:35

Nitesh

1

1

Welcome to Stack Overflow! While this code snippet may solve the question, including an explanation [really helps](//meta.stackexchange.com/q/114762) to improve the quality of your post. Remember that you are answering the question for readers in the future, not just the person asking now! Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight Nov 01 '16 at 09:45

score -1 · Answer 31 · edited Apr 19 '17 at 22:05

-1

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;

edited Apr 19 '17 at 22:05

EJoshuaS - Stand with Ukraine

11,977
56
49
78

answered Apr 19 '17 at 16:31

user3115408

1

Please include some explanation for this. – EJoshuaS - Stand with Ukraine Apr 19 '17 at 22:05

score -1 · Answer 32 · edited May 11 '17 at 13:12

-1

I think this should work:

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

edited May 11 '17 at 13:12

Aurasphere

3,841
12
44
71

answered May 10 '17 at 18:25

Darshit Raj

11

The max and min for varchar, compare the values based on alphabetical order not on length – sia Feb 09 '18 at 22:42

score -1 · Answer 33 · edited Aug 18 '17 at 14:47

-1

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;

edited Aug 18 '17 at 14:47

Cody Gray - on strike

239,200
50
490
574

answered Jun 29 '17 at 04:26

Thuat Nguyen

71
3

score -1 · Answer 34 · answered Mar 24 '21 at 16:44

-1

For Oracle Database:

select city,length(city) from (select * from station order by length(city),city) where rownum=1

UNION

select city,length(city) from (select * from station order by length(city) desc,city) where rownum=1;

answered Mar 24 '21 at 16:44

Akshma Mittal

69
1
5

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

34 Answers34

Linked