LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » DELETE CONSECUTIVE DUPLICATES

DELETE CONSECUTIVE DUPLICATES

Options

Previous Topic · Next Topic

moonsulu

Posted: Friday, August 14, 2009 10:16:54 PM

Rank: Newbie

Joined: 3/18/2009
Posts: 6
Points: 18

hi guys, i have a table this way:

id keyword
1 banana
2 apple
3 cherry
4 orange
5 orange
6 apple
7 orange
8 melon
9 blackberry
10 orange
11 orange
12 orange
13 melon
14 melon
15 melon
16 melon

and i want this table to look this way:

id keyword
1 banana
2 apple
3 cherry
4 orange
6 apple
7 orange
8 melon
9 blackberry
10 orange
13 melon

how do i write the right sql statement for that?

thanks for any help.

bklr

Posted: Monday, August 17, 2009 3:55:57 AM

Rank: PFY

Joined: 1/21/2009
Posts: 27
Points: 81
Where do you live?: India

try this one
declare @tab table(id int,keyword varchar(32))
insert into @tab select
1,'banana' union all select
2,'apple' union all select
3,'cherry' union all select
4,'orange' union all select
5,'orange' union all select
6,'apple' union all select
7,'orange' union all select
8,'melon' union all select
9,'blackberry' union all select
10,'orange' union all select
11,'orange' union all select
12,'orange' union all select
13,'melon' union all select
14,'melon' union all select
15,'melon' union all select
16,'melon'

select * from @tab

delete t from @tab t
inner join (select *,(select 1 from @tab where id = t.id - 1 and keyword = t.keyword)as val from @tab t) s on s.id = t.id
where val = 1

select * from @tab

moonsulu

Posted: Monday, August 17, 2009 5:58:23 AM

Rank: Newbie

Joined: 3/18/2009
Posts: 6
Points: 18

tnx very much bklr. i will try that.

bklr

Posted: Tuesday, August 18, 2009 3:45:35 AM

Rank: PFY

Joined: 1/21/2009
Posts: 27
Points: 81
Where do you live?: India

welcome

Scott Whigham

Posted: Tuesday, August 18, 2009 5:03:22 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX

FYI - if you are on SQL 2008, you can take advantage of the new INSERT syntax:

Code:

DECLARE @tab TABLE(id INT,keyword VARCHAR(32))
INSERT @tab
VALUES
  (1,'banana')
  , (2,'apple')
  , (3,'cherry')
  , (4,'orange')
  , (5,'orange')
  , (6,'apple')
  , (7,'orange')
  , (8,'melon')
  , (9,'blackberry')
  , (10,'orange')
  , (11,'orange')
  , (12,'orange')
  , (13,'melon')
  , (14,'melon')
  , (15,'melon')
  , (16,'melon')

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.