How do I select the longest 'string' from a table when grouping
MysqlMysql Problem Overview
Example:
SELECT partnumber, manufacturer, condition, SUM(qty), AVG(price), description FROM parts
WHERE [something]
GROUP BY partnumber, manufacturer, condition
I have some descriptions that are blank, and there can be many partnumber, manufacturer, condition values, and on the group it seems to take the first description available, which can be blank. Id like to get the longest description available.
i tried this:
MAX(LENGTH(description))
however that returns the number of characters in the string. Is it possible to do what im trying to do in MySQL?
Mysql Solutions
Solution 1 - Mysql
Try ORDER BY LENGTH(description) DESC and use LIMIT 1 to only get the largest.
Solution 2 - Mysql
ORDER BY LENGTH(description) DESC LIMIT 1
This will sort the results from longest to shortest and give the first result (longest.)
Solution 3 - Mysql
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`
Solution 4 - Mysql
MAX(LENGTH(description)) returns length of longest value in Description column.
Solution 5 - Mysql
If a "description" contains multi-idiom characters, then you should use
MAX(CHAR_LENGTH(description))
For example :
SELECT LENGTH("Это тест, связанный с длиной строки в базе данных")
Resulting in 89.
Whereas :
SELECT CHAR_LENGTH("Это тест, связанный с длиной строки в базе данных")
Resulting in 49.
Solution 6 - Mysql
I found a solution. MAX(description) seems to work just fine.