求查询同一表内多字段同时重复记录并保留最小ID记录SQL语句

Question

select j1.* from jxs_CarTypePrice j1,jxs_CarTypePrice j2
where j1.ID<>j2.ID and 
j1.[Carname]=j2.[Carname]
      and j1.[dealerPrice]=j2.[dealerPrice]
      and j1.[factoryPrice]=j2.[factoryPrice]
      and j1.[pic]=j2.[pic]
      and j1.[grade]=j2.[grade]
      and j1.[year]=j2.[year]
      and j1.[drive]=j2.[drive]
      and j1.[configuration]=j2.[configuration]
      and j1.[dimensions]=j2.[dimensions]
      and j1.[quality]=j2.[quality]
      and j1.[engine]=j2.[engine]
      and j1.[oilWear]=j2.[oilWear]
      and j1.[gearbox]=j2.[gearbox]
      and j1.[warranty]=j2.[warranty]
      and j1.[dealer]=j2.[dealer]
      and j1.[area]=j2.[area]这个语句是把所有的重复记录都查出了，但是我想只删除里面重复的一条记录
而不是把重复的记录都删除，请问SQL语句啥写？？
例子：就是把重复的记录只删除一条（红框里的记录），保留一条

Answer 1

select j1.* from jxs_CarTypePrice j1
where not exists (
select 1 from jxs_CarTypePrice j2
where j1.ID>j2.ID and 
j1.[Carname]=j2.[Carname]
      and j1.[dealerPrice]=j2.[dealerPrice]
      and j1.[factoryPrice]=j2.[factoryPrice]
      and j1.[pic]=j2.[pic]
      and j1.[grade]=j2.[grade]
      and j1.[year]=j2.[year]
      and j1.[drive]=j2.[drive]
      and j1.[configuration]=j2.[configuration]
      and j1.[dimensions]=j2.[dimensions]
      and j1.[quality]=j2.[quality]
      and j1.[engine]=j2.[engine]
      and j1.[oilWear]=j2.[oilWear]
      and j1.[gearbox]=j2.[gearbox]
      and j1.[warranty]=j2.[warranty]
      and j1.[dealer]=j2.[dealer]
      and j1.[area]=j2.[area]
)

Answer 2

你这个条件 j1.ID<>j2.ID
貌似有问题吧

Answer 3

删除语句delete j1
from jxs_CarTypePrice j1
where exists (
select 1 from jxs_CarTypePrice j2
where j1.ID>j2.ID and 
j1.[Carname]=j2.[Carname]
      and j1.[dealerPrice]=j2.[dealerPrice]
      and j1.[factoryPrice]=j2.[factoryPrice]
      and j1.[pic]=j2.[pic]
      and j1.[grade]=j2.[grade]
      and j1.[year]=j2.[year]
      and j1.[drive]=j2.[drive]
      and j1.[configuration]=j2.[configuration]
      and j1.[dimensions]=j2.[dimensions]
      and j1.[quality]=j2.[quality]
      and j1.[engine]=j2.[engine]
      and j1.[oilWear]=j2.[oilWear]
      and j1.[gearbox]=j2.[gearbox]
      and j1.[warranty]=j2.[warranty]
      and j1.[dealer]=j2.[dealer]
      and j1.[area]=j2.[area]
)