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!
2
Answers
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:
(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.
No need for aggregation.
order by
andlimit
are good enough:If you want to allow ties, one solution is to use window functions: