Averen Posted October 8, 2006 Share Posted October 8, 2006 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? Quote Link to comment Share on other sites More sharing options...
Firemankurt Posted October 9, 2006 Share Posted October 9, 2006 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] Quote Link to comment Share on other sites More sharing options...
fenway Posted October 9, 2006 Share Posted October 9, 2006 Are you running a version with subquery support (4.1+)? Quote Link to comment Share on other sites More sharing options...
Averen Posted October 9, 2006 Author Share Posted October 9, 2006 That I am. :) Quote Link to comment Share on other sites More sharing options...
fenway Posted October 10, 2006 Share Posted October 10, 2006 Then I don't know what you mean by "fails". Quote Link to comment Share on other sites More sharing options...
Averen Posted October 11, 2006 Author Share Posted October 11, 2006 Oh you were asking the guy that posted the SQL not me, haha.Anyway, that sql doesn't work for me either. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 11, 2006 Share Posted October 11, 2006 You can probably do with with a derived table that gets the max length entry by name, and then JOINing in the original table. Quote Link to comment Share on other sites More sharing options...
Averen Posted October 12, 2006 Author Share Posted October 12, 2006 Ok I'll try that, thanks. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.