Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

delete duplicate rows

hi there ,
how can i delete duplicate rows
dmt_campaign_id

1008
1008
1008
1008
1007
1006
1005
1005
1008
1002
1006

i want it to be

1008
1007
1006
1005
1002
0
nav29
Asked:
nav29
1 Solution
 
Shanmuga SundaramDirector of Software EngineeringCommented:
0
 
openshacCommented:
Microsoft have got a comprehensive article here:
http://support.microsoft.com/kb/139444
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi nav,

If you have the OLAP extensions available, this is a piece of cake. If not, it's still workable, just a bit wordier.

The code below is a complete test example. Just modify the DELETE statement to match your table.



Kent

create table t (v integer);
insert into t values (1);
insert into t values (1);
insert into t values (1);
insert into t values (1);
insert into t values (1);
select * from t;
delete from (select v, row_number () over (partition by v) as c from t) as a
where c > 1;
select * from t;

Open in new window

0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now