duplicate rows - 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

SQL Server 2000 Forums

Transact-SQL (2000)

duplicate rows

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-04-11 : 11:13:24

Hi,

I have the following table:
CREATE TABLE [oas_stmdocline] (
[stmcode] [varchar] (12) COLLATE Compatibility_138_409_20003 NOT NULL ,
[stmnum] [varchar] (12) COLLATE Compatibility_138_409_20003 NOT NULL ,
[cmpcode] [varchar] (12) COLLATE Compatibility_138_409_20003 NOT NULL ,
[doccode] [varchar] (12) COLLATE Compatibility_138_409_20003 NOT NULL ,
[docnum] [varchar] (12) COLLATE Compatibility_138_409_20003 NOT NULL ,
[doclinenum] [int] NOT NULL ,
[trancode] [varchar] (12) COLLATE Compatibility_138_409_20003 NOT NULL ,
[acclookup] [varchar] (32) COLLATE Compatibility_138_409_20003 NULL ,
[srcorcontra] [smallint] NOT NULL ,
[valuestm] [money] NOT NULL ,
[valuestm_dp] [smallint] NOT NULL ,
[statrecint] [int] NULL ,
[statrec] [smallint] NULL ,
[accode] [varchar] (79) COLLATE Compatibility_138_409_20003 NULL ,
[valuedoc] [money] NOT NULL ,
[valuedoc_dp] [smallint] NOT NULL ,
[docrate] [money] NULL ,
[valuehome] [money] NULL ,
[valuehome_dp] [smallint] NULL ,
[valuedual] [money] NULL ,
[valuedual_dp] [smallint] NULL ,
[dualrate] [money] NULL ,
[statuser] [varchar] (1) COLLATE Compatibility_138_409_20003 NULL ,
[linetype] [smallint] NULL ,
[debitcredit] [smallint] NULL ,
[duedate] [datetime] NULL ,
[valdate] [datetime] NULL ,
[taxcode1] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[taxvalue1] [money] NULL ,
[taxvalue1_dp] [smallint] NULL ,
[taxcode2] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[taxvalue2] [money] NULL ,
[taxvalue2_dp] [smallint] NULL ,
[taxcode3] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[taxvalue3] [money] NULL ,
[taxvalue3_dp] [smallint] NULL ,
[taxcode4] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[taxvalue4] [money] NULL ,
[taxvalue4_dp] [smallint] NULL ,
[taxcode5] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[taxvalue5] [money] NULL ,
[taxvalue5_dp] [smallint] NULL ,
[taxcode6] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[taxvalue6] [money] NULL ,
[taxvalue6_dp] [smallint] NULL ,
[taxcode7] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[taxvalue7] [money] NULL ,
[taxvalue7_dp] [smallint] NULL ,
[taxcode8] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[taxvalue8] [money] NULL ,
[taxvalue8_dp] [smallint] NULL ,
[taxcode9] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[taxvalue9] [money] NULL ,
[taxvalue9_dp] [smallint] NULL ,
[descr] [varchar] (36) COLLATE Compatibility_138_409_20003 NULL ,
[extref1] [varchar] (32) COLLATE Compatibility_138_409_20003 NULL ,
[extref2] [varchar] (32) COLLATE Compatibility_138_409_20003 NULL ,
[extref3] [varchar] (32) COLLATE Compatibility_138_409_20003 NULL ,
[extref4] [varchar] (32) COLLATE Compatibility_138_409_20003 NULL ,
[extref5] [varchar] (32) COLLATE Compatibility_138_409_20003 NULL ,
[extref6] [varchar] (32) COLLATE Compatibility_138_409_20003 NULL ,
[elcurr1] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[elvalue1] [money] NULL ,
[elvalue1_dp] [smallint] NULL ,
[elrate1] [money] NULL ,
[elcurr2] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[elvalue2] [money] NULL ,
[elvalue2_dp] [smallint] NULL ,
[elrate2] [money] NULL ,
[elcurr3] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[elvalue3] [money] NULL ,
[elvalue3_dp] [smallint] NULL ,
[elrate3] [money] NULL ,
[elcurr4] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[elvalue4] [money] NULL ,
[elvalue4_dp] [smallint] NULL ,
[elrate4] [money] NULL ,
[elcurr5] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[elvalue5] [money] NULL ,
[elvalue5_dp] [smallint] NULL ,
[elrate5] [money] NULL ,
[elcurr6] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[elvalue6] [money] NULL ,
[elvalue6_dp] [smallint] NULL ,
[elrate6] [money] NULL ,
[elcurr7] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[elvalue7] [money] NULL ,
[elvalue7_dp] [smallint] NULL ,
[elrate7] [money] NULL ,
[elcurr8] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[elvalue8] [money] NULL ,
[elvalue8_dp] [smallint] NULL ,
[elrate8] [money] NULL ,
[discsflag] [smallint] NULL ,
[discdate1] [datetime] NULL ,
[discrate1] [money] NULL ,
[discvalue1] [money] NULL ,
[discvalue1_dp] [smallint] NULL ,
[discdate2] [datetime] NULL ,
[discrate2] [money] NULL ,
[discvalue2] [money] NULL ,
[discvalue2_dp] [smallint] NULL ,
[discdate3] [datetime] NULL ,
[discrate3] [money] NULL ,
[discvalue3] [money] NULL ,
[discvalue3_dp] [smallint] NULL ,
[discdate4] [datetime] NULL ,
[discrate4] [money] NULL ,
[discvalue4] [money] NULL ,
[discvalue4_dp] [smallint] NULL ,
[discdate5] [datetime] NULL ,
[discrate5] [money] NULL ,
[discvalue5] [money] NULL ,
[discvalue5_dp] [smallint] NULL ,
[docsumtax] [money] NULL ,
[docsumtax_dp] [smallint] NULL ,
[taxlinecode] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[doctaxturn] [money] NULL ,
[doctaxturn_dp] [smallint] NULL ,
[ten99taxcode1] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[ten99taxvalue1] [money] NULL ,
[ten99taxvalue1_dp] [smallint] NULL ,
[ten99taxcode2] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[ten99taxvalue2] [money] NULL ,
[ten99taxvalue2_dp] [smallint] NULL ,
[ten99taxcode3] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[ten99taxvalue3] [money] NULL ,
[ten99taxvalue3_dp] [smallint] NULL ,
[medcode] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[bnkcode] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[elmbanktag] [smallint] NULL ,
[elmaddrtag] [smallint] NULL ,
[usrref1] [varchar] (35) COLLATE Compatibility_138_409_20003 NULL ,
[usrref2] [varchar] (35) COLLATE Compatibility_138_409_20003 NULL ,
[usrref3] [varchar] (35) COLLATE Compatibility_138_409_20003 NULL ,
[tradercode] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[posted] [smallint] NULL ,
[lineseqno] [int] NULL ,
[actdoccode] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[actdocnum] [varchar] (12) COLLATE Compatibility_138_409_20003 NULL ,
[actdoclinenum] [int] NULL
) ON [PRIMARY]
GO

I need to return the rows that have the same values for the fields:
stmcode and valdate and extref1 and valuestm.
If more than 1 row have identical data for all the fields i mentioned i must show those rows.

Any help will be appreciated.
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-11 : 11:16:23

[code]
select stmcode, valdate, extref1, valuestm
from oas_stmdocline
group by stmcode, valdate, extref1, valuestm
having count(*) > 1
[/code]

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-04-11 : 14:56:55

Thanks i'll try it. In the select i have to return all the fields in the table not just the fields stmcode, valdate, extref1, valuestm. So will i have to do group by to all the fields? In the having claues will i then have to write it as follows?
having (count(stmcode) > 1 and count(valdate) > 1 and count(extref1) > 1 and count(valuestm) > 1)

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-11 : 15:44:03

quote:
Originally posted by collie

Thanks i'll try it. In the select i have to return all the fields in the table not just the fields stmcode, valdate, extref1, valuestm. So will i have to do group by to all the fields? In the having claues will i then have to write it as follows?
having (count(stmcode) > 1 and count(valdate) > 1 and count(extref1) > 1 and count(valuestm) > 1)

Thanks

I gfuess it all comes down to, "What are you trying to do?"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-11 : 18:56:29

[code]
select a.*
from oas_stmdocline a
inner join
(
select stmcode, valdate, extref1, valuestm
from oas_stmdocline
group by stmcode, valdate, extref1, valuestm
having count(*) > 1
) b
on a.stmcode = b.stmcode
and a.valdate = b.valdate
and a.extref1 = b.extref1
and a.valuestm = b.valuestm
[/code]

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-04-12 : 03:19:47

thanks :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources