Removing duplicates rows by max date on ordered table

Question

I have the table Example Table with the following data

Example Table

ID	DATE	NAME
9	2021-04-13 21:39:00.569000	ABC
8	2020-12-17 16:49:17.903000	ABC
7	2020-12-16 16:49:17.903000	ABC
6	2020-06-09 09:55:52.005000	WER
5	2020-06-09 09:55:52.004000	WER
4	2020-06-08 09:48:43.318000	YTG
3	2020-06-05 14:51:42.860000	YTG
2	2020-04-28 13:58:30.972000	YTG
1	2020-04-25 13:58:30.972000	ABC

And I want to get for every distinct NAME in the table it's has max date until found a diffrent NAME. So the result set must be as the following

Expected Output

ID	DATE	NAME
9	2021-04-13 21:39:00.569000	ABC
6	2020-06-09 09:55:52.005000	WER
4	2020-06-08 09:48:43.318000	YTG
1	2020-04-25 13:58:30.972000	ABC

I tried sql query as below and got no result.

select NAME
from
(
  select NAME, max(DATE) - min(DATE) as diff
  from Example_Table
  group by NAME
) ex
order by diff desc;

Answer 1

Use lead():

select t.*
from (select t.*,
             lead(name) over (order by date) as next_name
      from t
     ) t
where next_name is null or next_name <> name;