Jump to content

Archived

This topic is now archived and is closed to further replies.

Averen

Grouping by text length.

Recommended Posts

Hey there,

I currently have a table

[table]
[tr][td]id[/td][td]name[/td][td]text[/td][/tr]

[tr][td]1[/td][td]var1[/td][td]text[/td][/tr]
[tr][td]2[/td][td]var1[/td][td]moretext[/td][/tr]
[tr][td]3[/td][td]var2[/td][td]text[/td][/tr]
[tr][td]4[/td][td]var2[/td][td]moretext[/td][/tr]

[/table]

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

eg

[table]
[tr][td]name[/td][td]text[/td][/tr]

[tr][td]var1[/td][td]moretext[/td][/tr]
[tr][td]var2[/td][td]moretext[/td][/tr]

[/table]

Anyone have any ideas?

Share this post


Link to post
Share on other sites
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.


[code]SELECT
    `name` ,
      CHAR_LENGTH(`name`)
AS
    `Length`
FROM 
      `table`
WHERE
      `Length`
IN (
    SELECT
            MAX(CHAR_LENGTH(`Length`))
    FROM 
            `table`
    ) [/code]

Share this post


Link to post
Share on other sites
Are you running a version with subquery support (4.1+)?

Share this post


Link to post
Share on other sites
Then I don't know what you mean by "fails".

Share this post


Link to post
Share on other sites
Oh you were asking the guy that posted the SQL not me, haha.

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

Share this post


Link to post
Share on other sites
You can probably do with with a derived table that gets the max length entry by name, and then JOINing in the original table.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.