Select shortest and longest string

Question

Is it possible to select the shortest and longest strings by characters in a table?

I have a CITY column of type VARCHAR(20) and I want to select the shortest and longest city names in alphabetical order by length.

I did like this

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 CITY ASC;

When ordered alphabetically, Let the CITY names be listed as ABC, DEF, PQRS, and WXY, with the respective lengths 3,3,4, and 3. The longest-named city is obviously PQRS, but there are options for the shortest-named city; I have to select ABC because it comes first alphabetically.

My query ended up with all three CITY having length 3.

ABC 3 DEF 3 PQRS 4 WXY 3

The result of SELECT must be

ABC 3 PQRS 4

Its duplicate question https://stackoverflow.com/questions/35397411/sql-query-for-finding-the-longest-name-and-shortest-name-in-a-table — Lav Vishwakarma, Jun 11 '18 at 09:06
@LAVKUMARVISHWAKARMA, note that the linked question is for some other dbms than MySQL. — jarlh, Jun 11 '18 at 09:10
Possible duplicate of [SQL query for finding the longest name and shortest name in a table](https://stackoverflow.com/questions/35397411/sql-query-for-finding-the-longest-name-and-shortest-name-in-a-table) — steffen, Jun 11 '18 at 09:12
@steffen, Oops, notíced that now when I scrolled down. Sorry LAVKUMARVISHWAKARMA. — jarlh, Jun 11 '18 at 09:15
What difference does 'alphabetical order' make? You mean if you have two cities 'Paris' and 'Milan', pick 'Milan'? — Strawberry, Jun 11 '18 at 10:44

score 24 · Accepted Answer · edited Jun 12 '18 at 11:58

24

Anyway i got the answer

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;

edited Jun 12 '18 at 11:58

Thomas Ayoub

29,063
15
95
142

answered Jun 12 '18 at 09:08

Midhun

513
1
6
13

3

What if you have a table with 6 cities: `XXX`, `YYY`, `ZZZ`, `OOOO`, `AAAAA`, `BBBBB`? Your query effectively becomes `SELECT CITY, LENGTH(CITY) FROM STATION WHERE LENGTH(CITY) IN (5, 3) ORDER BY LENGTH(CITY) DESC, CITY ASC LIMIT 2;` You would just get `AAAAA` and `BBBBB` as the results, which are the two longest city names, not the longest and the shortest. – The Unknown Dev Mar 21 '21 at 17:02
If you want the Hacker Rank solution, this one works. – fatfrog Jun 29 '22 at 14:41

score 9 · Answer 2 · edited Dec 07 '21 at 14:33

9

I know you have chosen your answer already, but here is a shorter answer that might help. This is using Microsoft MySQL Server but it can also be easily translated to any other type using the call LIMIT instead of TOP.

Shortest Length

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

Longest Length

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

edited Dec 07 '21 at 14:33

Federico Navarrete

3,069
5
41
76

answered Jun 25 '18 at 14:35

Jessica G.

101
7

2

MySQL and Microsoft SQL Server are different products. – NickG Jun 09 '21 at 09:50

score 4 · Answer 3 · answered Jun 11 '18 at 09:19

Try these queries.

Longest City Name::

select CITY from STATION where char_length(CITY) = (select max(char_length(CITY)) from STATION)

Shortest City Name::

select CITY from STATION where char_length(CITY) = (select min(char_length(CITY)) from STATION)

score 3 · Answer 4 · edited Dec 07 '21 at 14:33

I read the comment of Midhun Manohar above: https://stackoverflow.com/a/50813334/11129060

I'm kind of a newbie so these are easier for me to understand:

SELECT CITY, LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY) IN (
    SELECT MAX(LENGTH(CITY))
    FROM STATION
)
ORDER BY CITY ASC LIMIT 1;
                    
SELECT CITY, LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY) IN (
   SELECT MIN(LENGTH(CITY))
   FROM STATION
)
ORDER BY CITY ASC LIMIT 1;

or another post seems to be easier: https://stackoverflow.com/a/41285068/11129060

select CITY,LENGTH(CITY) from STATION order by Length(CITY) asc, CITY limit 1; 
select CITY,LENGTH(CITY) from STATION order by Length(CITY) desc, CITY limit 1;

score 3 · Answer 5 · answered Dec 22 '19 at 16:23

I have tried this and found the solution for MySQL database query,

(SELECT CITY, LENGTH(CITY)
FROM STATION 
ORDER BY LENGTH(CITY) ASC, CITY ASC LIMIT 1)
UNION
(SELECT  CITY, LENGTH(CITY)
FROM STATION 
ORDER BY LENGTH(CITY) DESC, CITY ASC LIMIT 1)

I think it is better to use LIMIT than row number.

score 1 · Answer 6 · edited Jun 13 '18 at 20:33

1

I think you need union with subquery if I understand correctly :

select s.*
from station s
where length(city) in (select max(length(city)) 
                       from station 
                       union 
                       select min(length(city)) 
                       from station)
order by length(city);

edited Jun 13 '18 at 20:33

marc_s

732,580
175
1,330
1,459

answered Jun 11 '18 at 10:56

Yogesh Sharma

49,870
5
26
52

I edited with the help of your answer @yogesh-sharma – Midhun Jun 12 '18 at 08:40

score 1 · Answer 7 · edited Dec 07 '21 at 14:33

1

SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY),CITY LIMIT 1;

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

The above code should easily work out and be easy to understand. The first query displays the shortest city name and its length. The second one outputs the longest.

edited Dec 07 '21 at 14:33

Federico Navarrete

3,069
5
41
76

answered Nov 26 '20 at 22:34

leplandelaville

186
1
9

Kirill Novikov · Answer 8 · 2023-02-05T14:53:57.507

0

You can use UNION operator

(SELECT CITY, MAX(LENGTH(CITY)) FROM STATION)
UNION
(SELECT CITY, MIN(LENGTH(CITY)) FROM STATION)
GROUP BY CITY ASC LIMIT 1;

More examples https://dev.mysql.com/doc/refman/8.0/en/union.html

edited Feb 05 '23 at 14:53

answered Jun 11 '18 at 09:15

Kirill Novikov

2,576
4
20
33

yep my mistake instead of ORDER I meant GROUP – Kirill Novikov Feb 05 '23 at 14:53

score 0 · Answer 9 · edited Nov 02 '20 at 10:24

0

I found this one simple:

SELECT CITY, LENGTH(CITY) FROM (SELECT CITY FROM STATION ORDER BY LENGTH(CITY) ASC, CITY ASC) 
WHERE ROWNUM=1;

SELECT CITY, LENGTH(CITY) FROM (SELECT CITY FROM STATION ORDER BY LENGTH(CITY) DESC, CITY ASC) 
WHERE ROWNUM=1;

edited Nov 02 '20 at 10:24

Fateme Mirjalili

762
7
16

answered Jun 07 '19 at 10:32

jishadaniel

1
1

score 0 · Answer 10 · edited Jun 15 '20 at 14:55

0

please find this below query :

SELECT top 2 CITY,LEN(CITY)
FROM STATION
WHERE LEN(CITY) IN (
  SELECT MAX(LEN(CITY))
  FROM STATION
  UNION
  SELECT MIN(LEN(CITY))
  FROM STATION
)
ORDER BY LEN(CITY) DESC,CITY ASC ;

edited Jun 15 '20 at 14:55

Jaimil Patel

1,301
6
13

answered Jun 15 '20 at 09:33

Marni

11

1

Please add some explanation to your answer such that others can learn from it – Nico Haase Jun 15 '20 at 09:58

score 0 · Answer 11 · answered Nov 22 '20 at 20:39

0

Much cleaner way:

SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC, CITY ASC LIMIT 1;
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY ASC LIMIT 1;

answered Nov 22 '20 at 20:39

Kenny Aires

1,338
12
16

score 0 · Answer 12 · edited Jan 14 '21 at 07:03

0

In mssql server, it should be this.

SELECT TOP 2 CITY, LEN(CITY) FROM STATION
WHERE LEN(CITY) IN (
    SELECT MAX(LEN(CITY)) FROM STATION
    UNION
    SELECT MIN(LEN(CITY)) FROM STATION
)
ORDER BY LEN(CITY) DESC, CITY ASC;

edited Jan 14 '21 at 07:03

geertjanvdk

3,440
24
26

answered Jan 14 '21 at 02:31

Present Karmacharya

1

score 0 · Answer 13 · edited Jun 10 '21 at 08:33

0

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;

edited Jun 10 '21 at 08:33

Slava Rozhnev

9,510
6
23
39

answered Jun 10 '21 at 06:55

user16183786

1

2

Please add some explanation to your answer such that others can learn from it – Nico Haase Jun 10 '21 at 14:11
1

Welcome to Stack Overflow! While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Yunnosch Jun 17 '21 at 06:38

score 0 · Answer 14 · edited Oct 26 '21 at 07:19

0

(SELECT CITY, LENGTH(CITY) 
FROM STATION
ORDER BY LENGTH(CITY) ASC, CITY ASC LIMIT 1)
UNION
(SELECT CITY, LENGTH(CITY) 
FROM STATION
ORDER BY LENGTH(CITY) DESC, CITY DESC LIMIT 1);

This gives you result something like this:

ABC 3
PQRS 4

edited Oct 26 '21 at 07:19

Peter Csala

17,736
16
35
75

answered Oct 26 '21 at 07:17

Reshma Taj

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 26 '21 at 07:19

score 0 · Answer 15 · answered Nov 27 '21 at 20:18

The answer can be simplified in this sql query below.

(select city, length(city) from station group by city order by length(city), city limit 1)
union
(select city, length(city) from station group by city order by length(city) desc, city desc limit 1)

score 0 · Answer 16 · answered Dec 07 '21 at 14:31

0

For SQL Server 

SELECT CITY,TXTLEN
FROM 

(

SELECT  RANK() OVER(ORDER BY CITY ASC) IDX,CITY ,LEN(CITY) TXTLEN FROM STATION 
WHERE LEN(CITY) IN ( SELECT MIN (LEN(CITY))  FROM STATION) 

UNION 

SELECT  RANK() OVER(ORDER BY CITY DESC) IDX,CITY ,LEN(CITY) TXTLEN FROM STATION 
WHERE LEN(CITY) IN ( SELECT MAX (LEN(CITY))  FROM STATION) 

) AS FIN WHERE IDX=1 ORDER BY TXTLEN

answered Dec 07 '21 at 14:31

Certmax

1

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 07 '21 at 14:39

score 0 · Answer 17 · answered Feb 13 '22 at 23:48

The following code works in Oracle SQL. All the above solutions did not work for Oracle SQL btw! You may add Union in between. But this runs fine as well!

SELECT * FROM 
          (SELECT city,LENGTH(city) 
          from station 
          order by Length(city), city)
WHERE rownum <= 1; 
SELECT * FROM
          (SELECT city,LENGTH(city) 
          from station
          order by Length(city) desc)
WHERE rownum <= 1;

score 0 · Answer 18 · edited Jul 24 '22 at 05:14

0

HERE IS ONE

WITH t1 AS ( 
    SELECT MAX(LENGTH(city)) AS Max_length_city ,
    MIN(LENGTH(city)) AS Min_length_city 
    FROM station 
) 
SELECT city,LENGTH(city)
FROM station
WHERE LENGTH(city)=(SELECT Max_length_city FROM t1) OR LENGTH(city)=(SELECT Min_length_city FROM t1)
ORDER BY LENGTH(city) DESC,city ASC LIMIT 2

edited Jul 24 '22 at 05:14

Simas Joneliunas

2,890
20
28
35

answered Jul 22 '22 at 15:21

Mohamed yosri

1

score 0 · Answer 19 · answered Nov 02 '22 at 05:59

    Select CITY, LENGTH(CITY) FROM STATION 
WHERE ID IN (
SELECT ID FROM(Select ID,CITY,CLEN,ROW_NUMBER() OVER(ORDER BY CLEN ASC) AS RW FROM(
Select ID,CITY,LENGTH(TRIM(CITY)) CLEN, 
    DENSE_RANK() OVER(PARTITION BY LENGTH(CITY) ORDER BY CITY) 
    rnk from STATION ORDER BY CITY) WHERE rnk =1 order by CLEN asc) WHERE RW =1 
)
UNION ALL 
Select CITY, LENGTH(CITY) FROM STATION 
WHERE ID IN (
SELECT ID FROM(Select ID,CITY,CLEN,ROW_NUMBER() OVER(ORDER BY CLEN ASC) AS RW FROM(
Select ID,CITY,LENGTH(TRIM(CITY)) CLEN, 
    DENSE_RANK() OVER(PARTITION BY LENGTH(CITY) ORDER BY CITY) 
    rnk from STATION ORDER BY CITY) WHERE rnk =1 order by CLEN asc) WHERE RW = (SELECT 
                                                                                MAX(RW) FROM(Select ID,CITY,CLEN,ROW_NUMBER() OVER(ORDER BY CLEN ASC) AS RW FROM(
Select ID,CITY,LENGTH(TRIM(CITY)) CLEN, 
    DENSE_RANK() OVER(PARTITION BY LENGTH(CITY) ORDER BY CITY) 
    rnk from STATION ORDER BY CITY) WHERE rnk =1 order by CLEN asc)) 
)

Hello there! Welcome to the Stack Overflow. thank you for answering questions. please add some explanation to your answer to be more helpful. Thank you! — El.Hum, Nov 05 '22 at 05:29

Select shortest and longest string

19 Answers19