Delete records with duplicates and join in another table

I need to write a query (Microsoft SQL Server) to delete duplicates in the table Vehicle that have Vehicle.CarId = Car.CarId and having the same concatenation (CarId, CounterLimit, Kilometers).

Table Car:

CarId
-----
11111

Table Vehicle:

VehicleId     CarId      CounterLimit     Kilometers   
-----------------------------------------------------
1             11111               250         120000
2             23456               300         150000
3             11111               250         120000 (record duplicated with 1, should be deleted)

Could you please help me?

Answer

• Joining the table
• creating the rank based on carid,counter limit, kilometer. If all three are same it is considered as duplicate. If you need to add more or less number of columns in this criteria you can adjust this part
• next we take just one of the above row , meaning we eliminate the duplicates using rank_1 = 1

with rank as (

select
vehicle.vehicleid,
vehicle.carid,
vehicle.CounterLimit,
vehicle.Kilometers,

row_number() over(partition by vehicle.carid,vehicle.CounterLimit, vehicle.Kilometers order by vehicle.vehicleid) as rank_

from a vehicle
left join car
on Vehicle.CarId =car.carid
)

select * from rank where rank_ = 1