分组获得最近日期最大值

获取最近日期，按照ID分组
类似
ID 001 ：值202101 … ；202002…
ID 002 ：值202001 … ；202009…
…
输出
ID 001 ：值202101 …
ID 002 ：值202009…
…

SELECT a.ID,a.paymentdate,a.data1
FROM 表 AS a,(SELECT ID,MAX(paymentdate) AS btime FROM 表 WHERE ID!=0 GROUP BY ID) AS b 
WHERE a.ID!=0 AND a.ID=b.ID AND a.paymentdate=b.btime
GROUP BY a.ID,a.paymentdate,a.data1