SQL question- help!!

[Garros]

hi all
I have a table for events with dates like this:
2006-01-01
2006-01-06
2006-01-15
2006-02-01
2006-02-15
2006-05-20
2006-07-15
2007-01-01
2007-01-12
How can I run a SELECT that will return results for all the events in certain months/years? So, the above data would return:
Jan 2006
Feb 2006
May 2006
July 2006
Jan 2007

many thanx for replies -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[stumpted]

Hi, this should work for you

Code:
SELECT
column1
, column2
, DATE_FORMAT(date_column, '%b %Y') AS formatted_date
FROM some_table
ORDER BY YEAR(date_column) ASC, MONTH(date_column) ASC
;
hope it helps -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[jesussaves]

This certainly worked for me,

SELECT DISTINCT date_format( calendar_date, '%b %Y' ) AS calendar_date_disp, calendar_date
FROM calendar
GROUP BY calendar_date_disp
ORDER BY calendar_date ASC

best of luck
thanks -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[salisbury]

Your question is incorrect for two reasons.

1) the distinct works across the entire row and not just on a single column

2) the distinct is not necessary with the group by. However your group by clause must contain each column present in your select statement. Without that you may end up with incorrect values in your returned rows. See the manual for GROUP BY HIDDEN FIELDS. Note that all other databases would throw an error message for your code above for leaving the second column out of the group by clause.

I thought this way
have a good time -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --