Distinct function broken in MySQL 5.7

Question

I have the following code that has stopped working since MySQL 5.7 upgrade:

$myDB->contentArray = $myDB->executeAssoc("
       SELECT DISTINCT(YEAR(displayDate)) as year FROM
       `inlineItemData_standardList` 
       WHERE `inlineItemGroupID` = $inlineItemGroupID 
       ORDER BY `displayDate` desc");

Could anyone show me how I would replace the DISTINCT part in this? I have searched around and can find others with the same issue but without a lot of PHP knowledge, I can't seem to implement the correct thing. I tried using GROUP BY 'YEAR(displayDate)) as year' but can't seem to get it to work. Thanks in advance.

Maybe it has but not because of DISTINCT, More detail please with sample data and expected outcome. — P.Salmon, Nov 09 '20 at 12:44
"stopped working" . What error did it cause? Did it simply return no results or was it actually a fault report? — Martin, Nov 09 '20 at 12:44
DISTINCT is NOT a function. ```SELECT DISTINCT YEAR(displayDate) as `year` FROM ..``` — Akina, Nov 09 '20 at 12:50
Sorry for not being more clear. The error it’s throwing is: Expression #1 of ORDER BY clause is not in SELECT list, references column 'my_dbnew.inlineItemData_standardList.displayDate' which is not in SELECT list; this is incompatible with DISTINCT — stowbee, Nov 09 '20 at 13:39

score 1 · Accepted Answer · answered Nov 09 '20 at 13:02

The ORDER BY clause of a SELECT DISTINCT query must be consistent with the SELECT clause.

So:

SELECT DISTINCT YEAR(displayDate) as year 
FROM inlineItemData_standardList
WHERE inlineItemGroupID = ?
ORDER BY YEAR(displayDate) DESC
-- Or: ORDER BY `year` DESC

Side note: use prepared statements! Do not concatenate variables in the query string: this is both inefficient and unsafe. Recommended reading: How can I prevent SQL injection in PHP?

Distinct function broken in MySQL 5.7

1 Answers1