max(length(field)) in mysql

Question

If I say:

select max(length(Name)) 
  from my_table

I get the result as 18, but I want the concerned data also. So if I say:

select max(length(Name)), 
       Name 
  from my_table

...it does not work. There should be a self join I guess which I am unable to figure it out.

Can anyone please provide me a clue?

I would recommend that you consider using CHAR_LENGTH() instead of LENGTH(). CHAR_LENGTH() returns the length of a string in characters. LENGTH() returns its length in bytes. For multi-byte character sets these values can be different, and you are probably concerned with character length, not byte length. — Ike Walker, Mar 01 '10 at 19:17

Quassnoi · Answer 1 · 2018-10-22T07:10:43.517

185

SELECT  name, LENGTH(name) AS mlen
FROM    mytable
ORDER BY
        mlen DESC
LIMIT 1

edited Oct 22 '18 at 07:10

answered Mar 01 '10 at 17:26

Quassnoi

413,100
91
616
614

cjohn · Answer 2 · 2010-03-01T17:32:14.887

17

Edited, will work for unknown max() values:

select name, length( name )
from my_table
where length( name ) = ( select max( length( name ) ) from my_table );

edited Mar 01 '10 at 17:32

answered Mar 01 '10 at 17:15

cjohn

11,370
3
30
17

yes, but i want the concerned `name` which has maxlenght of 18 – JPro Mar 01 '10 at 17:16
1

ok I managed to get what I want like this `select max(length(Name)) as num1,Name from my_table group by Name having num1 = 18`, since I know from the first query that max is 18. But how to combine this to one query? – JPro Mar 01 '10 at 17:19
Ah, okay, I misread that. In MS SQL I would use select Name from my_table where length(Name) = (select max(length(Name)) from my_table), but I am fairly certain that is not correct MySQL syntax. – cjohn Mar 01 '10 at 17:20

score 8 · Answer 3 · answered Sep 01 '17 at 09:27

8

Ok, I am not sure what are you using(MySQL, SLQ Server, Oracle, MS Access..) But you can try the code below. It work in W3School example DB. Here try this:

SELECT city, max(length(city)) FROM Customers;

answered Sep 01 '17 at 09:27

Velizar Andreev Kitanov

381
4
11

2

with mysql; "this is incompatible with sql_mode=only_full_group_by" – bcag2 Apr 29 '21 at 11:54

score 6 · Answer 4 · answered Jun 10 '17 at 20:09

6

In case you need both max and min from same table:

    select * from (
(select city, length(city) as maxlen from station
order by maxlen desc limit 1)
union
(select city, length(city) as minlen from station
order by minlen,city limit 1))a;

answered Jun 10 '17 at 20:09

Suman

476
5
7

score 2 · Answer 5 · answered Mar 01 '10 at 17:22

2

Use:

  SELECT mt.name 
    FROM MY_TABLE mt
GROUP BY mt.name
  HAVING MAX(LENGTH(mt.name)) = 18

...assuming you know the length beforehand. If you don't, use:

  SELECT mt.name 
    FROM MY_TABLE mt
    JOIN (SELECT MAX(LENGTH(x.name) AS max_length
            FROM MY_TABLE x) y ON y.max_length = LENGTH(mt.name)

answered Mar 01 '10 at 17:22

OMG Ponies

325,700
82
523
502

@JPro: Check the explain plan, but I think Quassnoi's is likely the most optimal. – OMG Ponies Mar 01 '10 at 17:30

score 2 · Answer 6 · edited May 18 '15 at 09:08

2

Select URColumnName From URTableName Where length(URColumnName ) IN 
(Select max(length(URColumnName)) From URTableName);

This will give you the records in that particular column which has the maximum length.

edited May 18 '15 at 09:08

Jujhar Singh

3,641
5
29
38

answered Oct 09 '14 at 13:40

Merish Joseph

21
2

score 2 · Answer 7 · answered Apr 04 '19 at 16:52

2

Use CHAR_LENGTH() instead-of LENGTH() as suggested in: MySQL - length() vs char_length()

SELECT name, CHAR_LENGTH(name) AS mlen FROM mytable ORDER BY mlen DESC LIMIT 1

answered Apr 04 '19 at 16:52

Rajesh Goel

3,277
1
17
13

in my case, I want to know if datas in mediumtext could be copied in tinytext field so `length` is what I need ! – bcag2 Apr 29 '21 at 11:40

score 1 · Answer 8 · edited Mar 01 '10 at 17:23

1

select * 
from my_table 
where length( Name ) = ( 
      select max( length( Name ) ) 
      from my_table
      limit 1 
);

It this involves two table scans, and so might not be very fast !

edited Mar 01 '10 at 17:23

lexu

8,766
5
45
63

answered Mar 01 '10 at 17:19

Martin

9,674
5
36
36

The limit in the sub-query is unnecessary: max() is an aggregation operator and will only return 1 row. – Martin Mar 01 '10 at 19:37

score 0 · Answer 9 · answered Mar 01 '10 at 17:17

0

I suppose you could use a solution such as this one :

select name, length(name)
from users
where id = (
    select id
    from users
    order by length(name) desc
    limit 1
);

Might not be the optimal solution, though... But seems to work.

answered Mar 01 '10 at 17:17

Pascal MARTIN

395,085
80
655
663

max(length(field)) in mysql

9 Answers9

Linked