Jump to content


Photo

Grouping by text length.


  • Please log in to reply
7 replies to this topic

#1 Averen

Averen
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 08 October 2006 - 12:16 PM

Hey there,

I currently have a table









idnametext
1var1text
2var1moretext
3var2text
4var2moretext


Now I want to group this table by name, but have the group have the longest 'text'

eg







nametext
var1moretext
var2moretext


Anyone have any ideas?


#2 Firemankurt

Firemankurt
  • Members
  • PipPipPip
  • Advanced Member
  • 56 posts
  • LocationWashington State

Posted 09 October 2006 - 04:04 AM

I'm sorry.  I tried a number of things but this is as close as I can get so far.
It does not work yet but it may stimulate an idea for someone else.

The subquery works by itself but seems to fail as a subquery.


SELECT 
     `name` ,
      CHAR_LENGTH(`name`) 
AS 
     `Length` 
FROM  
      `table` 
WHERE 
      `Length` 
IN (
    SELECT 
            MAX(CHAR_LENGTH(`Length`)) 
     FROM  
            `table`
    )


#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 October 2006 - 05:57 AM

Are you running a version with subquery support (4.1+)?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 Averen

Averen
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 09 October 2006 - 03:26 PM

That I am. :)

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 October 2006 - 03:24 PM

Then I don't know what you mean by "fails".
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 Averen

Averen
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 11 October 2006 - 06:21 AM

Oh you were asking the guy that posted the SQL not me, haha.

Anyway, that sql doesn't work for me either.

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 October 2006 - 07:53 PM

You can probably do with with a derived table that gets the max length entry by name, and then JOINing in the original table.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 Averen

Averen
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 12 October 2006 - 05:53 AM

Ok I'll try that, thanks.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users