[thelist] MYSQL: getting distinct dates from a DATETIME field

[Joshua Olson]

From: "Tab Alleman" <Tab.Alleman at MetroGuide.com>
Sent: Thursday, October 02, 2003 3:25 PM


> $query = "SELECT DATE_FORMAT(event_date, '%b %D') AS event_dates,
> event_cost, event_name FROM event ORDER BY event_date ASC
> GROUP BY MONTH(event_date), DAY(event_date);"

I am not a MySQL guru, but the GROUPING matra for SQL is:

If a GROUP BY clause exists, you cannot select a non-aggregate column unless
it also appears in the GROUP BY clause.

This query violates that rule, and therefore will not work.

<tip type="SQL" author="Joshua Olson">
If your query has a GROUP BY clause, the SELECT clause can only contain
aggregated columns (SUM, MIN, COUNT, etc) or the columns that are in the
GROUP BY clause.

Write it up on your wall, post-it note it to your monitor, or whatever it
takes.  It's a common mistake when writing queries, but one that is easy to
spot, and often time correct.

As with every rule, there are some itsy bitys exceptions when the GROUP BY
columns are enforcably 1-1 with other columns in the table, which means
arguably you should be able to select them plain jane.  Again, those are
exceptions, and not too often found in the wild.
</tip>

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168