[thelist] MySQL GROUP BY Anomaly?

Joshua Olson joshua at waetech.com
Thu May 9 13:01:02 CDT 2002


----- Original Message -----
From: "Jay Blanchard" <jay.blanchard at niicommunications.com>
Sent: Thursday, May 09, 2002 1:51 PM
Subject: [thelist] MySQL GROUP BY Anomaly?


> but if I run this query against the table with millions of records I get
(a
> small snippet of the results)

> Can anyone enlighten me as to what is happening? Both tables are on the
same
> machine, but the one with millions of records is a MERGE table. If I run
the
> query against the individual tables in the merge, they return the proper
(3
> row) result.

Jay,

Check the "time" portion of the date field.  My bet is that each of the
records is indeed unique if you account for date and time.  Grouping by date
is sorta rough.  You'll either want to GROUP BY on a function of RecordDate
that normalizes the values to midnight, or create a view that normalizes the
RecordDate field to midnight and then run your query against the view.

The syntax of the normalizing function will greatly depend on what the
database has to offer.  And, I'm no MySQL guru... so, good luck.

HTH,
-joshua





More information about the thelist mailing list