query help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

kodumudisadha
Starting Member

33 Posts

Posted - 2009-03-06 : 02:19:22

in my table cm_number,gv_number 2 columns are there .
cm_number is unique and gv_number is duplicate.
now i want to delete duplicate gv_numbers without consider unique cm_number .
my both columns are varchar only.set rowcount function not allow to set.
please help.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-06 : 02:23:53

can u give some sample data and expected output
then better to understand

kodumudisadha
Starting Member

33 Posts

Posted - 2009-03-06 : 02:27:28

cm_number gvnumber
-------- ---------
1 1
2 1
3 1
4 2
5 2

expected output
-----------
cm_number gvnumber
-------- ---------
1 1
4 2

i want to delete duplicate gvnumbers.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-06 : 02:40:24

declare @t table(cm_number int, gvnumber int)
insert into @t select 1, 1
insert into @t select 2, 1
insert into @t select 3, 1
insert into @t select 4, 2
insert into @t select 5, 2

select cm_number , gvnumber from ( select row_number()over(partition by gvnumber order by cm_number) as rid , * from @t) t
where rid = 1

delete from @t where cm_number in
(select cm_number from ( select row_number()over(partition by gvnumber order by cm_number) as rid , * from @t) t
where rid > 1 )

select * from @t

kodumudisadha
Starting Member

33 Posts

Posted - 2009-03-06 : 03:22:20

Hi,
its showing row_number is not arecognized fncton

kodumudisadha
Starting Member

33 Posts

Posted - 2009-03-06 : 03:25:17

this version is sqlserver 2000

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-06 : 04:57:03

if cm_number is identity column use this
declare @t table(cm_number int, gvnumber int)
insert into @t select 1, 1
insert into @t select 2, 1
insert into @t select 3, 1
insert into @t select 4, 2
insert into @t select 5, 2

delete from @t where cm_number in
( select cm_number from (select * ,(select count(*) from @t where gvnumber = t.gvnumber and cm_number <= t.cm_number)as rid from @t t)s
where rid > 1 )

select * from @t

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-03-06 : 05:28:45

delete from @t1
where gvnumber in (select gvnumber from @t1 group by gvnumber having count(*) > 1)
and cm_number not in (select min(cm_number) from @t1 group by gvnumber having count(*) > 1)

select * from @t1

isk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources