CODEWITHSUNDEEP
mysqlsql
abhishek
abhishek

Reputation: 115

SHORTEST and LONGEST string ORDERED alphabetically in MYSQL?

I have a table structure like this:

      table users
+-------+--------------+
| Field |     Type     |
+-------+--------------+
|   id  |      INT     |
+-------+--------------+
|  name | VARCHAR(255) |
+-------+--------------+

I want to a single query that should return the longest and smallest NAME as well as their respective lengths when ordered alphabetically.

For e.g.

Suppose the data is (it's dummy) 

ABA, BBA, PQR, HITS, QUES, PQRST, ABCDE

I want the query to return 

+-------+---+
|  ABA  | 3 |
+-------+---+
| ABCDE | 5 |
+-------+---+

I tried:

(
SELECT minName, LENGTH(minName)
FROM (
    SELECT name AS minName 
    FROM users
    WHERE LENGTH(name)=(SELECT MIN(LENGTH(name)) FROM users)
    ) AS maxUsers
ORDER BY minName ASC
LIMIT 1
)
UNION
(
SELECT minName, LENGTH(minName)
FROM (
    SELECT name AS minName 
    FROM users
    WHERE LENGTH(name)=(SELECT MAX(LENGTH(name)) FROM users)
    ) AS maxUsers
ORDER BY minName ASC
LIMIT 1
)

How can I make this query short? Any one?

Upvotes: 2

Views: 8712

Answers (6)

Karthik S N
Karthik S N

Reputation: 1

Try this:

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

UNION

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

Upvotes: 0

Rubens Amaral
Rubens Amaral

Reputation: 85

It's better to use CHAR_LENGTH instead of LENGTH, otherwise you will get wrong results depending on the character set you are currently using. Please have a look at MySQL - length() vs char_length() LENGTH() returns the length of the string measured in bytes. CHAR_LENGTH() returns the length of the string measured in characters.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Here is a simpler query that returns all the names with the minimum and maximum length:

SELECT u.Name, LENGTH(u.Name) as len
FROM users u JOIN
     (SELECT MIN(LENGTH(Name)) as minl, MAX(LENGTH(Name)) as maxl
      FROM users u
     ) uu
     ON LENGTH(u.name) IN (uu.minl, uu.maxl)
ORDER BY u.Name;

You want one of each length. You can get this using an additional aggregation:

SELECT MIN(u.Name) as Name, LENGTH(u.Name) as len
FROM users u JOIN
     (SELECT MIN(LENGTH(Name)) as minl, MAX(LENGTH(Name)) as maxl
      FROM users u
     ) uu
     ON LENGTH(u.name) IN (uu.minl, uu.maxl)
GROUP BY LENGTH(u.Name)
ORDER BY MIN(name);

Upvotes: 4

Rajat Jain
Rajat Jain

Reputation: 11

Many solutions provide a solution which consists of multiple sub-queries which leads to a lot of confusion for SQL beginners. Here's a less complicated solution-

SELECT * FROM (
                  SELECT <COL_NAME>,LENGTH(<COL_NAME>) AS Length FROM <TABLE_NAME> 
                  ORDER BY 2,1 LIMIT 1
                  ) as MINIMUM 

    UNION ALL 

    SELECT * FROM (
                   SELECT <COL_NAME>,LENGTH(<COL_NAME>) AS Length FROM <TABLE_NAME> 
                   ORDER BY 2 DESC,1 LIMIT 1
                  ) as MAXIMUM;

Upvotes: 1

Madhurupa Moitra
Madhurupa Moitra

Reputation: 15

Try this: simpler and easier~

For the shortest

SELECT MIN(name), LENGTH(MIN(name)) FROM users WHERE LENGTH(name)=(SELECT MIN(LENGTH(name)) FROM users);

For the longest

SELECT MAX(name), LENGTH(MAX(name)) FROM users WHERE LENGTH(name)=(SELECT MAX(LENGTH(name)) FROM users);

Upvotes: 0

Kemal G&#252;ler
Kemal G&#252;ler

Reputation: 614

If you want to use UNION try this

(
    SELECT 
       name,
       LENGTH(name)
    FROM users
    ORDER BY LENGTH(name), name ASC
    LIMIT 1
)
UNION
(
    SELECT 
       name,
       LENGTH(name)
    FROM users
    ORDER BY LENGTH(name) DESC, name ASC
    LIMIT 1
)

Upvotes: 3

Related Questions