On Tue, 2004-07-13 at 13:51, Justin Swanhart wrote: > > 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?
Yup, check out: http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html > 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. Sorta, but its probably better to assume that its random. > 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. But there are 2 rows with that email which, considering 'email' is the only thing group'ed on, makes sense. From the link above, one would only select 'first' and 'last' if they knew they were unique as well and wanted to save on typing. Probably the only way to trust that is to have a unique constraint on those three columns. > 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". Yah, and Oracle actually enforces foreign keys and won't insert guessed default values for 'NOT NULL' fields when you try to insert a null value into them. MySQL keeps you on your toes! Garth > 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] > > > > -- . 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]
