php unique month/year from date column in database??

[osgood_]

OK, so I'm sure this used to work but I cant seem to see the wood from the trees.

I have a typical date format in the database and I am trying to get the unique month and year from the date column and then order the information in the correct order - month/year:

date

2022-01-10

2022-06-15

2022-11-24

2023-11-06

2022-06-17

2024-01-17

This gets the unique month and year

SELECT DISTINCT MONTHNAME(date) as `Month` , ' ', YEAR(date) AS `Year`
FROM destination_dates_prices

But when I try to ORDER BY date I get an error:

SELECT DISTINCT MONTHNAME(date) as `Month` , ' ', YEAR(date) AS `Year`
FROM destination_dates_prices ORDER BY date DESC

Mysql Error:

#3065 - Expression #1 of ORDER BY clause is not in SELECT list, references column 'destination_dates_prices.date' which is not in SELECT list; this is incompatible with DISTINCT

I can't seem to find where to locate the 'date' in the select clause???? 

Os

[Nancy OShea]

Wouldn't the logic be something like:

SELECT DESTINCT field_name
FROM table_name
ORDER BY field_name ASC|DESC;

[osgood_ · Accepted solution]

Well it gets a bit more complicated. After doing a Google search and finding bits and pieces of nearly working examples, but not quite what I required, this finally worked:

SELECT DISTINCT month(`date`), year(`date`), monthname(`date`) FROM destination_dates_prices 

ORDER BY year(`date`), month(`date`) ASC

I've done this numerous times, on a number of other websites, in a different way, BUT for some reason that didn't work. I dont know if its because I'm on a newer version of sql on this new machine or this database table is slightly more complex!!!

I must be a sucker for punishment!!!

[osgood_]

Thanks for marking that correct, I didnt like to do that myself!