(response bottom-posted) "Ed Reed" <[EMAIL PROTECTED]> wrote on 10/13/2005 12:36:24 PM:
> Can anyone please help me with this? I'm just not getting it. > > - Thanks > > >>> "Ed Reed" < [EMAIL PROTECTED] > 10/6/05 2:50:46 PM >>> > Thanks for the reply, > > Sorry; I'm using 4.1.11 > > Thanks again. > > >>> Pooly < [EMAIL PROTECTED] > 10/6/05 1:51 AM >>> > 2005/10/6, Ed Reed < [EMAIL PROTECTED] >: > > I'm trying to group some sub categories with a concatenated > result. I'm trying to get the max sub for each item per month. I > think it should be fairly easy but it is kicking my butt. My data > looks like this, > > > > +--------+--------+------+ > > | month | item | sub | > > +--------+--------+------+ > > | 8 | 5 | NULL | > > +--------+--------+------+ > > | 8 | 4 | a | > > +--------+--------+------+ > > | 8 | 6 | NULL | > > +--------+--------+------+ > > | 8 | 6 | a | > > +--------+--------+------+ > > | 8 | 5 | a | > > +--------+--------+------+ > > | 8 | 4 | b | > > +--------+--------+------+ > > | 9 | 1 | NULL | > > +--------+--------+------+ > > | 9 | 2 | NULL | > > +--------+--------+------+ > > | 9 | 1 | a | > > +--------+--------+------+ > > | 9 | 3 | NULL | > > +--------+--------+------+ > > | 9 | 2 | a | > > +--------+--------+------+ > > | 9 | 1 | b | > > +--------+--------+------+ > > | 9 | 4 | NULL | > > +--------+--------+------+ > > | 9 | 4 | a | > > +--------+--------+------+ > > | 9 | 2 | b | > > +--------+--------+------+ > > | 9 | 1 | c | > > +--------+--------+------+ > > | 10 | 1 | NULL | > > +--------+--------+------+ > > | 10 | 1 | a | > > +--------+--------+------+ > > | 10 | 2 | NULL | > > +--------+--------+------+ > > > > I'm not having a problem getting a concatenated result but I am > having difficulty getting my data grouped correctly. My results > should look like this. > > +-----------+ > > | MAX Group | > > +-----------+ > > | 8-4b | > > +-----------+ > > | 8-5a | > > +-----------+ > > | 8-6a | > > +-----------+ > > | 9-1c | > > +-----------+ > > | 9-2b | > > +-----------+ > > | 9-3 | > > +-----------+ > > | 9-4a | > > +-----------+ > > | 10-1a | > > +-----------+ > > | 10-2 | > > +-----------+ > > > > > > - Thanks in advance > > > > So, Max group by month/item ? > http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html > and for concat : > http://dev.mysql.com/doc/mysql/en/string-functions.html > > Since we don't know your MySQL version, I can't give you a precise answer. > HIMH > > -- > Pooly > Webzine Rock : http://www.w-fenec.org/ > Since you have neglected to post the name of your table or any of your previous attempts, would you be so kind as to try this query with the correct table and column names inserted: SELECT concat(month,'-',item,max(sub)) as 'MAX Group' FROM whatever_your_table_is_called GROUP BY monty, item; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
