max(length(field)) in mysql

SqlMysql

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

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

Solution 2 - Sql

Edited, will work for unknown max() values:

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

Solution 3 - Sql

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;

Solution 4 - Sql

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;

Solution 5 - Sql

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)

Solution 6 - Sql

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.

Solution 7 - Sql

Use CHAR_LENGTH() instead-of LENGTH() as suggested in: https://stackoverflow.com/questions/1734334/mysql-length-vs-char-length

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

Solution 8 - Sql

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 !

Solution 9 - Sql

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.

Categories
Recommended Sql Solutions
Recommended Mysql Solutions

Previous Solution:

What are the various WPF binding modes?

WpfData Binding

Next Solution:

Get selected option from select element

JqueryHtml Select

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionJProView Question on Stackoverflow
Solution 1 - SqlQuassnoiView Answer on Stackoverflow
Solution 2 - SqlcjohnView Answer on Stackoverflow
Solution 3 - SqlVelizar Andreev KitanovView Answer on Stackoverflow
Solution 4 - SqlSumanView Answer on Stackoverflow
Solution 5 - SqlOMG PoniesView Answer on Stackoverflow
Solution 6 - SqlMerish JosephView Answer on Stackoverflow
Solution 7 - SqlRajesh GoelView Answer on Stackoverflow
Solution 8 - SqlMartinView Answer on Stackoverflow
Solution 9 - SqlPascal MARTINView Answer on Stackoverflow