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?

Thanks,

Answer 1

Edited, will work for unknown max() values:

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

Answer 2

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.

Answer 3

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 !

Answer 4

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)

Answer 5

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