Selecting the ID of a row based on the max date in a group-CodePudding

If I have the following, say Table1 with columns ID, Plan and PlanDate How do I select the ID for the row with the max PlanDate for each Plan?

Thanks in advance

1 plan1 2021-10-07 18:18:28.723
2 plan2 2021-10-07 19:20:17.513
3 plan2 2021-10-07 19:28:44.580
4 plan2 2021-10-07 19:41:09.360
5 plan3 2021-10-07 19:42:09.360
6 plan3 2021-10-07 19:43:11.360

So I would like to end up with

1
4
6

CodePudding user response：

with cte as (
select ID, Plan, ROW_NUMBER() OVER(partition by plan order by planDate desc) as RNUM
from Table1
)
select Id from cte
where RNUM = 1