SQL按某字段去重 保留按某个字段排序最大值

select * from tablename as a
where not exists (
select 1 from tablename as b
 where b.name=a.name and b.id>a.id)

talename：要去重复的表

name:需要去重复的字段，可以有多个字段

ID：取id字段最大

实例：

查询这批数据ChildCode为000或00Y的，State不为1的，CatalogCode重复的情况下根据Cd_batch筛选最大的，如果Cd_batch相同根据TaskVersion筛选最大的数据
sql：

SELECT c.id,
c.rowGuid,
c.CatalogCode,
c.Cd_operation,
c.cd_batch,
c.TaskName,
c.TaskType,
c.Cd_time,
c.ItemCode,
c.TaskState,
c.DataSource FROM
(SELECT a.id,a.rowGuid,a.Cd_operation,a.cd_batch,a.TaskName,a.TaskType,a.Cd_time,a.ItemCode,a.DataSource,a.CatalogCode,a.TaskState,a.TaskVersion,a.ChildCode,a.State
FROM dn_task_directory a INNER JOIN
(SELECT ItemCode,MAX(Cd_batch) as max_cdbatch FROM dn_task_directory
GROUP BY ItemCode DESC) b
ON a.ItemCode=b.ItemCode AND a.Cd_batch=b.max_cdbatch) as c INNER JOIN
(SELECT ItemCode,MAX(TaskVersion) as max_taskversion
FROM dn_task_directory
GROUP BY ItemCode DESC) d ON c.ItemCode=d.ItemCode AND c.TaskVersion=d.max_taskversion
AND (c.ChildCode=‘000’ OR c.ChildCode=‘00Y’)
AND c.State !=‘1’