Re: Problem with GROUP BY
Learnt! On Wed, Oct 15, 2008 at 5:28 PM, philip <[EMAIL PROTECTED]> wrote: > > Date: Tue, 14 Oct 2008 16:55:11 +0300 > > From: Olexandr Melnyk <[EMAIL PROTECTED]> > > To: [EMAIL PROTECTED], mysql@lists.mysql.com > > Subject: Re: Problem with GROUP BY > > > > http://jan.kneschke.de/projects/mysql/groupwise-max > > > > 2008/10/14 Peter Brawley <[EMAIL PROTECTED]> > > > > > Philip > > > > > > mysql> SELECT number, MAX(event), name FROM info GROUP BY number; > > >> > > > > > > For discussion & examples see "Within-group aggregates" at > > > http://www.artfulsoftware.com/queries.php. > > Thank you both very much for your replies. > > Of course the solution is 'obvious' now I know the answer but as a > relative newcomer to MySQL I had spent the best part of a day trying to > find it. > > TTFN, > > Philip Riebold, [EMAIL PROTECTED] /"\ > Media Services\ / > University College London X ASCII Ribbon Campaign > Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail > London, W1T 4JF > +44 (0)20 7679 9259 (switchboard), 09259 (internal) > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Problem with GROUP BY
> Date: Tue, 14 Oct 2008 16:55:11 +0300 > From: Olexandr Melnyk <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED], mysql@lists.mysql.com > Subject: Re: Problem with GROUP BY > > http://jan.kneschke.de/projects/mysql/groupwise-max > > 2008/10/14 Peter Brawley <[EMAIL PROTECTED]> > > > Philip > > > > mysql> SELECT number, MAX(event), name FROM info GROUP BY number; > >> > > > > For discussion & examples see "Within-group aggregates" at > > http://www.artfulsoftware.com/queries.php. Thank you both very much for your replies. Of course the solution is 'obvious' now I know the answer but as a relative newcomer to MySQL I had spent the best part of a day trying to find it. TTFN, Philip Riebold, [EMAIL PROTECTED] /"\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (switchboard), 09259 (internal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with GROUP BY
http://jan.kneschke.de/projects/mysql/groupwise-max 2008/10/14 Peter Brawley <[EMAIL PROTECTED]> > Philip > > mysql> SELECT number, MAX(event), name FROM info GROUP BY number; >> > > For discussion & examples see "Within-group aggregates" at > http://www.artfulsoftware.com/queries.php. > > PB > > - > > philip wrote: > >> I created a table with, >> >> CREATE TABLE info ( >>number INTEGER UNSIGNED, >>event INTEGER UNSIGNED, >>name VARCHAR(2000) NOT NULL, >>PRIMARY KEY (number, event) >> ); >> >> and populated it with data to produce this, >> >> ++---+---+ >> | number | event | name | >> ++---+---+ >> | 67 | 1 | Alice | >> | 67 | 2 | Bob | >> | 69 | 1 | Carol | >> | 70 | 1 | Alex | >> | 71 | 1 | David | >> | 72 | 1 | Bob | >> | 72 | 2 | Alice | >> | 72 | 3 | David | >> ++---+---+ >> >> What I want to produce is a table with rows from the original with only >> the maximum value of event for each corresponding number selected, like >> this >> >> +++---+ >> | number | event | name | >> +++---+ >> | 67 | 2 | Bob | >> | 69 | 1 | Carol | >> | 70 | 1 | Alex | >> | 71 | 1 | David | >> | 72 | 3 | David | >> +++---+ >> >> The closest I have managed to produce using GROUP BY is, >> >> mysql> SELECT number, MAX(event), name FROM info GROUP BY number; >> +++---+ >> | number | MAX(event) | name | >> +++---+ >> | 67 | 2 | Alice | <- should be Bob >> | 69 | 1 | Carol | >> | 70 | 1 | Alex | >> | 71 | 1 | David | >> | 72 | 3 | Bob | <- should be David >> +++---+ >> >> I tried using a HAVING clause but got nowhere. >> >> Can anybody help please ? >> >> TTFN, >> >> Philip Riebold, [EMAIL PROTECTED] /"\ >> Media Services\ / >> University College London X ASCII Ribbon Campaign >> Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail >> London, W1T 4JF >> +44 (0)20 7679 9259 (switchboard), 09259 (internal) >> >> >> >> >> >> Internal Virus Database is out of date. >> Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: >> 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM >> >> >> > -- -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: Problem with GROUP BY
Philip mysql> SELECT number, MAX(event), name FROM info GROUP BY number; For discussion & examples see "Within-group aggregates" at http://www.artfulsoftware.com/queries.php. PB - philip wrote: I created a table with, CREATE TABLE info ( number INTEGER UNSIGNED, event INTEGER UNSIGNED, name VARCHAR(2000) NOT NULL, PRIMARY KEY (number, event) ); and populated it with data to produce this, ++---+---+ | number | event | name | ++---+---+ | 67 | 1 | Alice | | 67 | 2 | Bob | | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 1 | Bob | | 72 | 2 | Alice | | 72 | 3 | David | ++---+---+ What I want to produce is a table with rows from the original with only the maximum value of event for each corresponding number selected, like this +++---+ | number | event | name | +++---+ | 67 | 2 | Bob | | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 3 | David | +++---+ The closest I have managed to produce using GROUP BY is, mysql> SELECT number, MAX(event), name FROM info GROUP BY number; +++---+ | number | MAX(event) | name | +++---+ | 67 | 2 | Alice | <- should be Bob | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 3 | Bob | <- should be David +++---+ I tried using a HAVING clause but got nowhere. Can anybody help please ? TTFN, Philip Riebold, [EMAIL PROTECTED] /"\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (switchboard), 09259 (internal) Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM
Re: Re: Problem with GROUP BY ... DESC
> "Arjen" == Arjen G Lentz <[EMAIL PROTECTED]> writes:
Arjen> Hi,
Arjen> - Original Message -
Arjen> From: "AJ" <[EMAIL PROTECTED]>
>> > > Ver 11.15 Distrib 3.23.46, for pc-linux-gnu (i686)
>> > > The problem that has cropped up I have is that in all GROUP BY column
>> > > DESC
>> > > statements, the DESC is now not being recognized, and the query is being
>> > > returned in ascending order. If I use ORDER BY... DESC that works fine.
>>>
>> >I'm seeing the same bug on 3.23.45 (and maybe others but I can't remember
>> >all the versions I've played with recently). It does the same thing where
>> >it accepts the syntax but does not respect it (and returns rows in the
>> >default ASC ordering).
>>
>> The problem is apparently solved if you kluge with e.g.
>> select field, count(field) from table group by field order by field
>> desc but it IS a kluge.
This is now fixed in the MySQL 3.23.47 and MySQL 4.0.1 source trees.
Here is a patch for this:
= sql/sql_lex.h 1.34 vs edited =
*** /tmp/sql_lex.h-1.34-29757 Sun Oct 7 14:18:08 2001
--- edited/sql/sql_lex.hMon Dec 10 17:27:02 2001
***
*** 140,146
enum lex_states next_state;
enum enum_duplicates duplicates;
enum enum_tx_isolation tx_isolation;
! uint in_sum_expr,grant,grant_tot_col,which_columns, sort_default;
thr_lock_type lock_option;
boolcreate_refs,drop_primary,drop_if_exists,local_file;
bool in_comment,ignore_space,verbose;
--- 140,146
enum lex_states next_state;
enum enum_duplicates duplicates;
enum enum_tx_isolation tx_isolation;
! uint in_sum_expr,grant,grant_tot_col,which_columns;
thr_lock_type lock_option;
boolcreate_refs,drop_primary,drop_if_exists,local_file;
bool in_comment,ignore_space,verbose;
= sql/sql_yacc.yy 1.86 vs edited =
*** /tmp/sql_yacc.yy-1.86-29757 Tue Aug 21 20:06:00 2001
--- edited/sql/sql_yacc.yy Mon Dec 10 17:26:37 2001
***
*** 470,476
ULONGLONG_NUM
%type
! literal text_literal insert_ident group_ident order_ident
simple_ident select_item2 expr opt_expr opt_else sum_expr in_sum_expr
table_wild opt_pad no_in_expr expr_expr simple_expr no_and_expr
using_list
--- 470,476
ULONGLONG_NUM
%type
! literal text_literal insert_ident order_ident
simple_ident select_item2 expr opt_expr opt_else sum_expr in_sum_expr
table_wild opt_pad no_in_expr expr_expr simple_expr no_and_expr
using_list
***
*** 1869,1878
| GROUP BY group_list
group_list:
! group_list ',' group_ident
! { if (add_group_to_list($3,(bool) 1)) YYABORT; }
! | group_ident
! { if (add_group_to_list($1,(bool) 1)) YYABORT; }
/*
** Order by statement in select
--- 1869,1878
| GROUP BY group_list
group_list:
! group_list ',' order_ident order_dir
! { if (add_group_to_list($3,(bool) $4)) YYABORT; }
! | order_ident order_dir
! { if (add_group_to_list($1,(bool) $2)) YYABORT; }
/*
** Order by statement in select
***
*** 1883,1889
| order_clause
order_clause:
! ORDER_SYM BY { Lex->sort_default=1; } order_list
order_list:
order_list ',' order_ident order_dir
--- 1883,1889
| order_clause
order_clause:
! ORDER_SYM BY order_list
order_list:
order_list ',' order_ident order_dir
***
*** 1893,1900
order_dir:
/* empty */ { $$ = 1; }
! | ASC { $$ = Lex->sort_default=1; }
! | DESC { $$ = Lex->sort_default=0; }
limit_clause:
--- 1893,1900
order_dir:
/* empty */ { $$ = 1; }
! | ASC { $$ =1; }
! | DESC { $$ =0; }
limit_clause:
***
*** 2451,2459
ident '.' '*' { $$ = new Item_field(NullS,$1.str,"*"); }
| ident '.' ident '.' '*'
{ $$ = new Item_field((current_thd->client_capabilities & CLIENT_NO_SCHEMA ?
NullS : $1.str),$3.str,"*"); }
-
- group_ident:
- order_ident order_dir
order_ident:
expr { $$=$1; }
--- 2451,2456
Regards,
Monty
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Problem with GROUP BY ... DESC
Hi, - Original Message - From: "AJ" <[EMAIL PROTECTED]> > > > Ver 11.15 Distrib 3.23.46, for pc-linux-gnu (i686) > > > The problem that has cropped up I have is that in all GROUP BY column DESC > > > statements, the DESC is now not being recognized, and the query is being > > > returned in ascending order. If I use ORDER BY... DESC that works fine. > >I'm seeing the same bug on 3.23.45 (and maybe others but I can't remember > >all the versions I've played with recently). It does the same thing where > >it accepts the syntax but does not respect it (and returns rows in the > >default ASC ordering). > > The problem is apparently solved if you kluge with e.g. > select field, count(field) from table group by field order by field desc > but it IS a kluge. That wouldn't not be a kludge actually, since the SQL-99 standard does not allow ASC/DESC on GROUP BY at all. It is an extention in the MySQL server (see http://www.mysql.com/doc/S/E/SELECT.html), and basically it is a shortcut for exactly what you describe: an ORDER BY with the same fields as the earlier GROUP BY. I tried it too on a 3.23, and indeed it does appear to not be working. Contrary to what is described above though, there is no ordering at all in my output, not ascending either. So basically it is as you would expect it to be without any ORDER BY operation. Maybe the output others got was just sheer luck, as it depends on the order the server reads the data rows Anyway, I've sent a note to the developers about this, including a sample reproducing it. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fwd: Re: Problem with GROUP BY ... DESC
> > Ver 11.15 Distrib 3.23.46, for pc-linux-gnu (i686) > > > > The problem that has cropped up I have is that in all GROUP BY column DESC > > statements, the DESC is now not being recognized, and the query is being > > returned in ascending order. If I use ORDER BY... DESC that works fine. > >I'm seeing the same bug on 3.23.45 (and maybe others but I can't remember >all the versions I've played with recently). It does the same thing where >it accepts the syntax but does not respect it (and returns rows in the >default ASC ordering). The problem is apparently solved if you kluge with e.g. select field, count(field) from table group by field order by field desc but it IS a kluge. AJ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with GROUP BY ... DESC
On Tue, 4 Dec 2001, AJ wrote: > Ver 11.15 Distrib 3.23.46, for pc-linux-gnu (i686) > > The problem that has cropped up I have is that in all GROUP BY column DESC > statements, the DESC is now not being recognized, and the query is being > returned in ascending order. If I use ORDER BY... DESC that works fine. I'm seeing the same bug on 3.23.45 (and maybe others but I can't remember all the versions I've played with recently). It does the same thing where it accepts the syntax but does not respect it (and returns rows in the default ASC ordering). -dave /*== www.urth.org We await the New Sun ==*/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
