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

[Tab Alleman]

Joshua Olson wrote:
> 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.
> 

Ok, this should work:

>> $query = "MONTH(event_date), DAY(event_date),
>> event_cost, event_name FROM event ORDER BY event_date ASC
>> GROUP BY MONTH(event_date), DAY(event_date);"

And if you really need MONTH/DAY to appear in one field, you can
hopefully do some kind of string concatenation in MySQL and give it an
alias.