[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