[thelist] MYSQL: getting distinct dates from a DATETIME field
rudy
rudy937 at rogers.com
Fri Oct 3 07:33:16 CDT 2003
> My efforts stopped after trying DISTINCT and GROUP BY in various forms
DISTINCT will work
your query was
SELECT DATE_FORMAT(event_date, '%b %D') AS event_dates
, event_cost , event_name
FROM event
ORDER BY event_date ASC
an observation: your specific DATE_FORMAT obscures the year, but i'm
guessing you don't have multiple years with the same month/day causing your
problem
i would recommend making your ORDER BY agree with the DATE_FORMAT
notice your alias for the DATE_FORMAT is event_dates with an "s" but you are
trying to sort the result set by event_date without an "s"
not sure how much of an effect that might have, without seeing your data
okay, so add DISTINCT to your query and change the ORDER BY --
SELECT DISTINCT
DATE_FORMAT(event_date, '%b %D') AS event_dates
, event_cost , event_name
FROM event
ORDER BY DATE_FORMAT(event_date, '%b %D') ASC
some databases allow you to use an alias in the ORDER BY, but i prefer not
to
when you use DISTINCT, a sort is implicit, and you can often leave it out,
but to be sure, you have to include it
rudy
More information about the thelist
mailing list