Aggregate function in MySQL-CodePudding

I have the following table in MySQL which has only two columns.

First_name Last_name
---------------------------
Babara     MacCaffrey
Ines       Brushfield
Freddi     Boagey
Ambur      Roseburgh
Clemmie    Betchley
Elka       Twiddell
Ilene      Dowson
Thacher    Naseby
Romola     Rumgay
Levy       Mynett

I want to extract the first name which has maximum length (in terms of number of characters) along with its length value as well.

I run the below query: select first_name, max(length(trim(first_name))) from customers;

It returns the below output which is partially incorrect.

Babara  7

while the length (7) is correct, the returned first name is not correct. Babara has only 6 chars (I made sure it does not have any leading and trailing space).

Can someone please help me why this is happening?

Thanks in advance!

CodePudding user response：

I want to extract the first name which has maximum length (in terms of number of characters) along with its length value as well.

No need for aggregation. order by and limit are good enough:

select name, char_length(trim(name)) name_length
from customers
order by name_length desc limit 1

If you want to allow ties, one solution is to use window functions:

select name, name_length
from (
    select name, char_length(trim(name)) name_length,
        rank() over(order by char_length(trim(name)) desc) rn
    from customers
) t
where rn = 1

CodePudding user response：

max returns the maximum value of an expression for each grouping, or for all rows if no GROUP BY is specified. In the latter case, it also causes all rows to be implicitly grouped into one. It does nothing to other columns being selected. So first_name will return the value from an arbitrary one of the rows being grouped (or, if using the recommended and default sql_mode ONLY_FULL_GROUP_BY, result in an error), not anything to do with the length.

You want just:

select first_name, char_length(trim(first_name))
from customers
order by char_length(trim(first_name)) desc, first_name
limit 1

(ordering by name as well so you get a deterministic result in the case of ties)

Note that you should almost never use length; use char_length instead.