Delete duplication based on info of one column

Question

I would like to eliminate duplication based on company id. I dont care which of the records under the model name will be deleted / stay.

I have this results:

+------------+------------------+
| company id | model name       |
+------------+------------------+
|    1       | chevrolet camaro |
|    1       | chevrolet spark  |
|    1       | chevrolet cruze  |
|    2       | mercedes c class |
|    2       | mercedes E class |
+------------+------------------+

And I would like to get these results:

+------------+------------------+
| company id | model name       |
+------------+------------------+
|    1       | chevrolet camaro |
|    2       | mercedes c class |
+------------+------------------+

Or these results(The point is I don't care which of the model name will be eliminated):

+------------+------------------+
| company id | model name       |
+------------+------------------+
|    1       | chevrolet spark  |
|    2       | mercedes E class |
+------------+------------------+

What should I do?

Possible duplicate of [How can I remove duplicate rows?](https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Roman Koliada, Dec 11 '17 at 13:24

score 2 · Accepted Answer · answered Dec 11 '17 at 13:21

2

You can use a group by:

select companyid, min(modelname) as modelname
from t
group b companyid;

answered Dec 11 '17 at 13:21

Gordon Linoff

1,242,037
58
646
786

I'd just add that @Moyshe may wish to create a staging table shaped like his initial table with `create table B like table A`, insert results in by putting `insert into B` before the select statement, and finish up with `rename table A as A_old` and then `rename table B as A` and optionally `drop table A_old` – Brian Dewhirst Dec 11 '17 at 13:25

score 0 · Answer 2 · answered Dec 11 '17 at 13:28

0

Try this

ALTER IGNORE TABLE 'yourTableName' ADD UNIQUE ('company id');

reference : here

answered Dec 11 '17 at 13:28

Nivethi Mathan

105
1
8

Yuri · Answer 3 · 2017-12-11T14:10:03.237

I would try this approach:

declare @tbl table (ID int, car nvarchar(100))
declare @tblTemp table (ID int, car nvarchar(100))
insert into @tbl(ID, car)
values
( 1 ,'chevrolet camaro'),
(1 ,'chevrolet spark'),  
(1 ,'chevrolet cruze'),  
(2 ,'mercedes c class'), 
(2 ,'mercedes E class'), 
(1 ,'chevrolet camaro'),
(1 ,'chevrolet spark'),  
(1 ,'chevrolet cruze'),  
(2 ,'mercedes c class'), 
(2 ,'mercedes E class')

insert into @tblTemp
select distinct  MAX(id), car from @tbl
group by car

delete from @tbl


insert into @tbl (car)
select car from @tblTemp

declare @i int  
  SELECT @i= ISNULL(MAX(id),0)  FROM @tbl


update @tbl
set id  = @i , @i = @i + 1
where id is null
select * from @tbl

SQL result:

Delete duplication based on info of one column

3 Answers3