SQL query to find duplicate and remove by giving some conditions

1

Hello all
I need support with SQL code.. Actually I my dataset I have few duplicates id and I want to remove the one where coverage is basic if the Id is duplicate. In my attachment,I provided the input table and the output table which I am really looking for.
Can any one help with the code.

IMG_20210116_203940
IMG_20210116_2039403000×4000 1.92 MB

2

Welcome. How is work at Bank of India? very cute picture but would prefer if you provided proper DDL and DML

declare @a table(id int, product varchar(50), coverage varchar(50), 
QUantity int )

insert into @
select 123, 'a', 'b', 1

etc

3

hi

hope this helps ..

drop table #sample_data 

create table #sample_data 
(
id       int , 
product  varchar(20), 
coverage varchar(20), 
Quantity int 
)
go 


insert into #sample_data select 126,'Tractor-111','Basic',125 
insert into #sample_data select 126,'Tractor-111','Extended',140 
insert into #sample_data select 125,'Tractor-111','Basic',1000 
go 

; with cte as 
(
   select 
          id
		, max(coverAGE) as maxcov
   from 
       #sample_data 
   group by id 
) 
select 
   a.* 
from  
   #sample_data A 
      join 
   cte b 
      on A.id = B.id and A.coverage = B.maxcov

image

1 Like
4

All depends on how the data is .. ( i mean data scenarios )

if its always duplicates and Basic and Extended then my SQL is valid
other wise
Some other SQL needs to be written ..