Sort by date and group by name - latest appointment for every person with names in descending order

My table in MySQL 8.0.29 looks like this

Name	Appointment
Bob	2022-06-01
Bob	2022-06-03
John	2022-06-02

I'm trying to get the latest appointment for every person with the names in descending order

The query should return

Name	Appointment
John	2022-06-02
Bob	2022-06-03

I've tried

SELECT * FROM (SELECT * FROM Table ORDER BY Appointment DESC) AS temp GROUP BY Name ORDER BY Name DESC;

But it doesn't return the latest dates for each person

Name	Appointment
John	2022-06-02
Bob	2022-06-01

by SomebodyOnEarth in StackOverflow on July 02, 2022.

Answer

You should select the max appointment date for each person:

SELECT   name, MAX(appointment)
FROM     mytable
GROUP BY name
ORDER BY name DESC

by Mureinik on July 02, 2022.

Other helpful answers

You can use GROUP By

SELECT NAme, MAX(Appointment) FROM mytable GROUP BY Name

Or otu can use Window functions

SELECT Name,Appointment
FROM (
SELECT Name, Appointment, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Appointment DESC) rn) t1
WHERE rn = 1

by nbk on July 02, 2022.