CODEWITHSUNDEEP
mysql
user1336827
user1336827

Reputation: 1826

How do I select the longest 'string' from a table when grouping

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. I'd 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 I'm trying to do in MySQL?

Upvotes: 126

Views: 94081

Answers (6)

StilesCrisis
StilesCrisis

Reputation: 16290

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

Upvotes: 241

user1336827
user1336827

Reputation: 1826

I found a solution. MAX(description) seems to work just fine.

Upvotes: -1

Tushar Kesare
Tushar Kesare

Reputation: 790

MAX(LENGTH(description)) returns length of longest value in Description column.

Upvotes: 6

Rubens Amaral
Rubens Amaral

Reputation: 85

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.

Upvotes: 3

Scott Nelson
Scott Nelson

Reputation: 889

ORDER BY LENGTH(description) DESC LIMIT 1

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

Upvotes: 35

eggyal
eggyal

Reputation: 125865

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`

Upvotes: 6

Related Questions