How do I select the longest 'string' 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 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?

StilesCrisis · Accepted Answer · 2023-02-17T12:03:57.533

224

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

edited Feb 17 '23 at 12:03

answered Jun 04 '12 at 14:06

StilesCrisis

15,972
4
39
62

score 31 · Answer 2 · answered Dec 19 '12 at 21:34

31

ORDER BY LENGTH(description) DESC LIMIT 1

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

answered Dec 19 '12 at 21:34

Scott Nelson

839
9
8

score 6 · Answer 3 · answered Jun 04 '12 at 14:16

6

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`

answered Jun 04 '12 at 14:16

eggyal

122,705
18
212
237

This seems to me to 'expensive', in running time and written code, for the selection of one record. I don't think a sub-query is the best way to handle the problem. – MJH Nov 29 '15 at 12:14
1

@rosa: you're right about verbosity, though I'm not sure performance time would be greatly different (both this and ORDER BY require filesort). Advantage of this version is that it returns all records of maximal length, not just a single indeterminate one. – eggyal Nov 29 '15 at 12:39
1

@rosa: also, looking back over this question, I think my understanding was that the OP wanted to obtain the longest string in each group, rather than the longest result overall. – eggyal Nov 30 '15 at 16:15

score 3 · Answer 4 · edited Nov 12 '21 at 07:52

3

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

edited Nov 12 '21 at 07:52

Ruli

2,592
12
30
40

answered Oct 17 '18 at 14:38

Tushar Kesare

700
8
20

score 2 · Answer 5 · edited Nov 12 '21 at 06:12

2

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.

edited Nov 12 '21 at 06:12

Blackcoat77

1,574
1
21
31

answered Nov 11 '21 at 18:37

Rubens Amaral

75
8

score 0 · Answer 6 · edited Nov 12 '21 at 07:54

0

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

edited Nov 12 '21 at 07:54

Ruli

2,592
12
30
40

answered Jun 04 '12 at 14:12

user1336827

1,728
2
15
30

8

That won't give you the *longest* description, rather the description which is the lexical maximum (i.e. in an alphabetical ordering). However, since that ordering will place any non-empty description after empty ones, it will always result in a non-empty description if one exists: perhaps this is sufficient for your needs? – eggyal Jun 04 '12 at 14:13

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

6 Answers6

Linked