How to select the longest row from a table when grouping

Question

Example:

SELECT partnumber, manufacturer, condition, SUM(qty), AVG(price), description FROM parts WHERE [something] GROUP BY partnumber, manufacturer, condition 

I have several descriptions that are empty, and there can be many part numbers, manufacturers, condition values, and in the group it seems that the first description available is available, which may be empty. Id would like to get the longest description.

I tried this:

 MAX(LENGTH(description)) 

however, returns the number of characters in the string. Is it possible to do what I'm trying to do in MySQL?

Answer 1

Try ORDER BY LENGTH(description) DESC and use LIMIT 1 to get the largest.

Answer 2

 ORDER BY LENGTH(description) DESC LIMIT 1 

This will sort the results from the longest to the shortest and give the first result (the longest.)

Answer 3

 SELECT partnumber, manufacturer, `condition`, SUM(qty), AVG(price), description FROM parts WHERE [something] AND LENGTH(description) = ( SELECT MAX(LENGTH(description)) FROM parts AS p WHERE p.partnumber = parts.partnumber AND p.manufacturer = parts.manufacturer AND p.condition = parts.condition ) GROUP BY partnumber, manufacturer, `condition` 

Answer 4

It seems I answered my question, MAX (description) seems to work fine.