On Tue, 2004-07-13 at 11:13, Justin Swanhart wrote:
> You are doing an implicit group by of first, last as
> well as your explicit group by of email.
>
> So you could have two records with the same e-mail
> address generate two records with your group by
>
> Justin Time [EMAIL PROTECTED]
> Justin Credible [EMAIL PROTECTED] --DUPE--
Where is the implicit group? The 'order by' shouldn't effect how things
are grouped. On MySQL 4.0.17:
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]
