Return 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 2005 Forums

Transact-SQL (2005)

Return duplicate rows

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-01 : 05:34:38

My table and rows as follow,

declare @t1 table
(idx smallint identity(1,1),cd smallint,seq tinyint,remk varchar(100));
insert into @t1 values(2,1,'p1');
insert into @t1 values(2,2,'p11');
insert into @t1 values(2,1,'p14');
insert into @t1 values(2,3,'p12');
insert into @t1 values(4,1,'p15');
insert into @t1 values(2,1,'p17');
insert into @t1 values(5,3,'p13');
insert into @t1 values(2,3,'p13');
insert into @t1 values(1,3,'p13');
insert into @t1 values(2,3,'p13');
select * from @t1;

@t1(cd,seq) is unique

How to return duplicate rows as follow,


idx   | cd    | seq   | remk
----------------------------------
1	2	1	p1
3	2	1	p14
4	2	3	p12
6	2	1	p17
8	2	3	p13
10	2	3	p13

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 05:52:04

[code]declare @t1 table
(idx smallint identity(1,1),cd smallint,seq tinyint,remk varchar(100));
insert into @t1 values(2,1,'p1');
insert into @t1 values(2,2,'p11');
insert into @t1 values(2,1,'p14');
insert into @t1 values(2,3,'p12');
insert into @t1 values(4,1,'p15');
insert into @t1 values(2,1,'p17');
insert into @t1 values(5,3,'p13');
insert into @t1 values(2,3,'p13');
insert into @t1 values(1,3,'p13');
insert into @t1 values(2,3,'p13');

select idx , cd , seq , remk from
(
select idx , cd , seq , remk,count(idx) over (partition by cd,seq) as cnt
from @t1
)t
where cnt>1
order by idx

output
------------------------------------
idx cd seq remk
1 2 1 p1
3 2 1 p14
4 2 3 p12
6 2 1 p17
8 2 3 p13
10 2 3 p13

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asuni
Yak Posting Veteran

55 Posts

Posted - 2010-04-01 : 05:54:48

SELECT idx, cd, seq, remk
FROM @t1
WHERE cd IN ( SELECT cd FROM @t1 GROUP BY cd HAVING COUNT(*) > 1 ) AND
seq IN ( SELECT seq FROM @t1 GROUP BY seq HAVING COUNT(*) > 1 )

IS IT OK?

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-01 : 06:03:08

both answer really great. tq

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 07:12:22

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources