Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mysql

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?

like image 757
user1336827 Avatar asked Jun 04 '12 14:06

user1336827


People also ask

How do you find the longest string length in SQL?

(SELECT min(len(<string_column>)) FROM<table_name> ) ; Example : SELECT TOP 1 * FROM friends WHERE len(firstName) = (SELECT min(len(firstName)) FROM friends);

How do you find the largest string in a list?

Use Python's built-in max() function with a key argument to find the longest string in a list. Call max(lst, key=len) to return the longest string in lst using the built-in len() function to associate the weight of each string—the longest string will be the maximum.

How do I select a length in SQL?

SQL Server LEN() Function The LEN() function returns the length of a string. Note: Trailing spaces at the end of the string is not included when calculating the length. However, leading spaces at the start of the string is included when calculating the length.

How do you select the shortest and longest name in SQL?

select city, length(city) from (select city from STATION order by length(city) DESC, city ASC) where rownum = 1 union select city, length(city) from (select city from STATION order by length(city), city ASC) where rownum = 1; The idea is to get the longest and shortest city then union them into one result.


2 Answers

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

like image 90
StilesCrisis Avatar answered Sep 30 '22 07:09

StilesCrisis


ORDER BY LENGTH(description) DESC LIMIT 1 

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

like image 32
Scott Nelson Avatar answered Sep 30 '22 08:09

Scott Nelson