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

Question

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

osgood_ · Accepted Answer

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!!!

Nancy OShea · Answer

Wouldn't the logic be something like: SELECT DESTINCT field_nameFROM table_nameORDER BY field_name ASC|DESC;

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded