MySQL return one occurrence of duplicate values

Question

Trying to figure out if there is a MySQL query for the following scenario:

Database example

id---fruit------date
01---apple------1990-01-01
02---banana-----2020-01-01
03---orange-----2021-02-03
04---apple------1999-12-12
05---pear-------2009-01-02
06---apple------2008-09-18
07---banana-----2007-11-12

I want a query to return all rows but for rows with the same fruit only one occurence and preferably the occurence with the latest date. So based on the above the query would return:

id---fruit------date
02---banana-----2020-01-01
03---orange-----2021-02-03
05---pear-------2009-01-02
06---apple------2008-09-18

Does this answer your question? [Select info from table where row has max date](https://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date) — Martin Zeitler, Sep 02 '21 at 03:17

score 0 · Answer 1 · answered Sep 02 '21 at 03:31

SELECT * FROM `table_name`
WHERE
id
IN
(

SELECT SUBSTRING_INDEX(temp,"|",1) `id` FROM
(
SELECT *,GROUP_CONCAT(id ORDER BY DATE DESC SEPARATOR "|") temp FROM `table_name`
GROUP BY fruit
)
AS dbx

)

Explaination:

Group_concat Id with Order by date. At this time I user separator "|"
Get position of that char to return latest ID
Select again from that table

MySQL return one occurrence of duplicate values

1 Answers1