Unique Query By Date with highest ID taking precidence-CodePudding

I am trying to get the newest unique dates, returning the date and the item. I have made a example here, but its not giving me unique dates only.

SELECT DISTINCT(date), meal, dinner_id FROM dinner_tb ORDER BY `dinner_tb`.`date` DESC LIMIT 10

See there are three items on the 16th, where I should only have the Pie on that day. Any suggestions please. MYSQL v8. dinner_id is primary key.

CodePudding user response：

MYSQL v8.

WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY `date` ORDER BY dinner_id DESC) rn
              FROM dinner_tb )
SELECT *
FROM cte
WHERE rn = 1;