How to remove the duplicates from the table

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)

How to remove the duplicates from the table

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-02-02 : 01:13:26

How to remove the duplicates from the table leaving the highest one

col1 col2
12 456
19 345
45 346
12 289
78 678
12 900

I need to remove the duplicates from col1. here col1 contains duplicate value(12). But instead of removing all the
3 rows, i need to remove all except the highest one(i.e 12-900). i have to remove other 2 rows(12-456.12-289)

G. Satish

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-02-02 : 01:23:36

SELECT col1, MAX(col2) FROM table
GROUP BY col1

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-02-02 : 01:25:31

google broken is it?
Seriously, I put your very first sentence into google and got a million hits.
This site has tons of examples, you really don't have to create a new topic for this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 01:26:21

DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY col2 DESC) AS Seq
FROM Table
)t
WHERE Seq>1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 01:31:57

quote:
Originally posted by AvanthaSiriwardana

SELECT col1, MAX(col2) FROM table
GROUP BY col1

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

i dont think you read question carefully. question was to delete all except the one with maxvalue.

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-02-02 : 01:45:51

quote:
Originally posted by visakh16

quote:
Originally posted by AvanthaSiriwardana

SELECT col1, MAX(col2) FROM table
GROUP BY col1

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

i dont think you read question carefully. question was to delete all except the one with maxvalue.

i think the same thing about you.

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-02 : 01:54:22

we can write avantha query as
Declare @t table
(col1 int,col2 int)

--Insert sample data
Insert into @t values(12,456)
Insert into @t values(19,345)
Insert into @t values(45,346)
Insert into @t values(12,289)
Insert into @t values(78,678)
Insert into @t values(12,900)

delete from @t where col2 not in ( SELECT MAX(col2) FROM @t
GROUP BY col1 )

select * from @t

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-02-02 : 01:58:21

thats why i said bklr.

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-02-02 : 02:05:24

quote:
Originally posted by visakh16

DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY col2 DESC) AS Seq
FROM Table
)t
WHERE Seq>1

This one looks a most perfect solution than others.

Rahul Shinde

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 02:08:22

quote:
Originally posted by ra.shinde

quote:
Originally posted by visakh16

DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY col2 DESC) AS Seq
FROM Table
)t
WHERE Seq>1

This one looks a most perfect solution than others.

Rahul Shinde

thanks Rahul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 02:10:29

quote:
Originally posted by bklr

we can write avantha query as
Declare @t table
(col1 int,col2 int)

--Insert sample data
Insert into @t values(12,456)
Insert into @t values(19,345)
Insert into @t values(45,346)
Insert into @t values(12,289)
Insert into @t values(78,678)
Insert into @t values(12,900)

delete from @t where col2 not in ( SELECT MAX(col2) FROM @t
GROUP BY col1 )

select * from @t

still not correct boss

you may be better off testing this well before suggesting. see below

Declare @t table
(col1 int,col2 int) 

--Insert sample data
Insert into @t values(12,456)
Insert into @t values(19,345)
Insert into @t values(45,346)
Insert into @t values(12,289)
Insert into @t values(78,678)
Insert into @t values(12,900)
Insert into @t values(13,900)
Insert into @t values(13,1200)
--bklr
delete from @t where col2 not in ( SELECT MAX(col2) FROM @t
GROUP BY col1 )

Declare @t1 table
(col1 int,col2 int) 

--Insert sample data
Insert into @t1 values(12,456)
Insert into @t1 values(19,345)
Insert into @t1 values(45,346)
Insert into @t1 values(12,289)
Insert into @t1 values(78,678)
Insert into @t1 values(12,900)
Insert into @t1 values(13,900)
Insert into @t1 values(13,1200)

--visakh
delete t1 from @t1 t1 where col2 not in ( SELECT MAX(col2) FROM @t1  WHERE col1=t1.col1
GROUP BY col1 )

select * from @t
select * from @t1

output
--------------------------------
bklr
--------------------------------
col1	col2
19	345
45	346
78	678
12	900
13	900
13	1200

visakh
---------------------------------
col1	col2
19	345
45	346
78	678
12	900
13	1200

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-02 : 02:23:14

k thats fine visakh

my code will fails in some cases

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 02:29:25

No probs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 02:35:59

quote:
Originally posted by AvanthaSiriwardana

quote:
Originally posted by visakh16

quote:
Originally posted by AvanthaSiriwardana

SELECT col1, MAX(col2) FROM table
GROUP BY col1

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

i dont think you read question carefully. question was to delete all except the one with maxvalue.

i think the same thing about you.

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

Hope now you understood what i meant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources