> Where is the implicit group? The 'order by'
> shouldn't effect how things
> are grouped. On MySQL 4.0.17:
Wow. I wasn't aware. Is that expected behavior?
Other databases (Oracle) generate an error when you
include columns in the select list that aren't in a
group by, or they do an implicit group by (postgres)
on the columns. I [wrongly] assumed an implicit group
by was going on because the query hadn't generated an
error.
I guess mysql just takes the values from the first
record that matches the group expression.
That creates confusion when you do:
select first,last,email, count(*)
from foobar
group by email
Because the query will report a count of two (given
your data) when there really is only one row with that
first,last,email combination.
Oracle would require you to do:
select first,last,email, count(*)
from foobar
group by first,last,email
otherwise you would get an error that "first is not a
GROUP BY expression".
That query would return four rows on your data, each
with a count of 1.
My apologies,
Justin
>
> create table bar ( email varchar(64), first
> varchar(64), last
> varchar(64) );
>
> insert into bar values ('[EMAIL PROTECTED]', 'a', 'smith'),
> ('[EMAIL PROTECTED]', 'a',
> 'williams'), ('[EMAIL PROTECTED]', 'b', 'webb'), ('[EMAIL PROTECTED]',
> 'c', 'oconner');
>
> mysql> select * from bar;
> +---------+-------+----------+
> | email | first | last |
> +---------+-------+----------+
> | [EMAIL PROTECTED] | a | smith |
> | [EMAIL PROTECTED] | a | williams |
> | [EMAIL PROTECTED] | b | webb |
> | [EMAIL PROTECTED] | c | oconner |
> +---------+-------+----------+
>
> mysql> select first,last,email from bar b group by
> b.email order by
> b.first, b.last;
> +-------+-------+---------+
> | first | last | email |
> +-------+-------+---------+
> | a | smith | [EMAIL PROTECTED] |
> | b | webb | [EMAIL PROTECTED] |
> +-------+-------+---------+
>
>
> > Case differences between the records could also
> cause
> > dupes. If case differences are causing it then do
> >
> > select lower(first), lower(last), lower(email)
> > ...
> > group by lower(first), lower(last), lower(email)
>
> Case or extra whitespace is a definite possibility.
> Aaron, try to find
> at least one occurrence of duplicate email addresses
> and then post a
> small (5 row) dataset that exhibits the problem you
> are having.
>
> Garth
>
> > --- Wesley Furgiuele <[EMAIL PROTECTED]> wrote:
> > > What type of field is the email field?
> > >
> > > Wes
> > >
> > > On Jul 13, 2004, at 11:04 AM, Aaron Wolski
> wrote:
> > >
> > > > Hey all,
> > > >
> > > > Got this query:
> > > >
> > > > SELECT first,last,email FROM CustomerTable AS
> t1,
> > > > OrderTable AS t2, CartTable AS t3 WHERE
> > > t2.cart_id=t3.cart_id
> > > > AND t1.id=t2.customer_index AND
> t3.submitted='1'
> > > AND
> > > > t3.product_index='1' AND t3.quantity>0
> > > > GROUP BY t1.email ORDER BY t1.first,t1.last
> > > >
> > > > For some strange reason it doesn't seem to
> group
> > > the email addresses.
> > > > I'd be hard pressed to find every occurrence
> out
> > > of 1000 records, but I
> > > > DID quickly spot two exact same records which
> > > means the email address
> > > > was not grouped.
> > > >
> > > > What can I do or where did I go wrong?
> > > >
> > > > Thanks!
> > > >
> > > > Aaron
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > >
> >
>
http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> > >
> --
> . Garth Webb
> . [EMAIL PROTECTED]
> .
> . shoes * 鞋子 * schoenen * 단화 * chaussures *
> zapatos
> . Schuhe * παπούτσια * pattini * 靴 *
> sapatas * ботинки
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
>
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
