Need to remove duplicate on 2 col -Edited now

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to remove duplicate on 2 col -Edited now

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-02-10 : 01:40:23

First of all..sorry friends, Before i posted wrongly the requirement.

Below is my requirement. Hope this will be clear now..

col1 col2 col3 col4 col5......col70
12 456 33 6
19 345 45 0
45 346 56 91
12 456 33 11
78 678 70 5
12 456 33 3

I need to remove the duplicates from above table. Observe the rows
with (12-456-33) is repeating. Now i want to remove 2 rows among the three repetative based on col4. Col4 has a highest value 11 for repetative rows. so by keeping 4th row in table and remove other duplicate rows.

G. Satish

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-10 : 01:51:32

select * from
( select * ,row_number() over( partition by col1 order by col2 desc) as rn from tbl1) t
where t.rn = 1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-10 : 01:58:04

Try this Once,


declare @temp table (col1 int,col2 int)
insert into @temp
select 12, 456 union all
select 19, 345  union all
select 45, 346 union all
select 12, 289  union all
select 78, 678 union all
select 12, 900

delete t from 
( select * ,row_number() over( partition by col1 order by col2 desc) as rn from @temp) t
where t.rn > 1

select * from @temp

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-02-10 : 01:59:08

This query is based on only col1. I need to filter the data based on two columns col1, col2.

quote:
Originally posted by Nageswar9

select * from
( select * ,row_number() over( partition by col1 order by col2 desc) as rn from tbl1) t
where t.rn = 1

G. Satish

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-10 : 02:00:51

Try the query
declare @temp table (col1 int,col2 int)
insert into @temp
select 12, 456 union all
select 19, 345 union all
select 45, 346 union all
select 12, 289 union all
select 78, 678 union all
select 12, 900

delete t from
( select * ,row_number() over( partition by col1 order by col2 desc) as rn from @temp) t
where t.rn > 1

select * from @temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 02:19:00

quote:
Originally posted by Nageswar9

Try the query
declare @temp table (col1 int,col2 int)
insert into @temp
select 12, 456 union all
select 19, 345 union all
select 45, 346 union all
select 12, 289 union all
select 78, 678 union all
select 12, 900

delete t from
( select * ,row_number() over( partition by col1 order by col2 desc) as rn from @temp) t
where t.rn > 1

select * from @temp

actually you dont need * in derived table. you just need rn value only for deletion

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-10 : 02:21:28

quote:
Originally posted by visakh16

quote:
Originally posted by Nageswar9

Try the query
declare @temp table (col1 int,col2 int)
insert into @temp
select 12, 456 union all
select 19, 345 union all
select 45, 346 union all
select 12, 289 union all
select 78, 678 union all
select 12, 900

delete t from
( select * ,row_number() over( partition by col1 order by col2 desc) as rn from @temp) t
where t.rn > 1

select * from @temp

actually you dont need * in derived table. you just need rn value only for deletion