duplciate - 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)

duplciate

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2008-06-23 : 11:32:31

this will give me the duplicate...
select id,Num, Type , opt from
(
SELECT ROW_NUMBER() OVER (partition BY Num ORDER BY id desc) AS ROWID,id, Type,opt
from table1
)tbl1
where rowid > 1

include the row=1 in the report, but with rowid must have more than 1. How do I get that...

thanks

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-23 : 11:36:57

Im not quite following your question. If you provide an example dataset and your desired result I believe it would be more understandable.

hai
Yak Posting Veteran

84 Posts

Posted - 2008-06-23 : 11:42:12

Sorry,

select rowid,Num, Type , opt from
(
SELECT ROW_NUMBER() OVER (partition BY Num ORDER BY id desc) AS ROWID,num, Type,opt
from table1
)tbl1
where rowid > 1

I want to display all records that have duplicated.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-23 : 11:47:51

[code]select t.id, t.num, t.type, t.opt
from table1 t
join ( select num, id
from table1
group by num, id
having count(*) > 1
) a on t.num = a.num
and t.id = a.id [/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 11:58:52

select t.id, t.num, t.type, t.opt
from table1 t
where num in (select num
from table1
group by num
having count(*) > 1)

hai
Yak Posting Veteran

84 Posts

Posted - 2008-06-23 : 12:05:45

What if the partition contain more then 1 field,...

eg:
select rowid,Num, Type , opt from
(
SELECT ROW_NUMBER() OVER (partition BY Num, field1, field3 ORDER BY id desc) AS ROWID,num, Type,opt
from table1
)tbl1
where rowid > 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 12:12:47

quote:
Originally posted by hai

What if the partition contain more then 1 field,...

eg:
select rowid,Num, Type , opt from
(
SELECT ROW_NUMBER() OVER (partition BY Num, field1, field3 ORDER BY id desc) AS ROWID,num, Type,opt
from table1
)tbl1
where rowid > 1

include them in group by as well

select t.id, t.num, t.type, t.opt
from table1 t
join (select num, field1, field3
from table1
group by num, field1, field3
having count(*) > 1)tmp
on tmp.num =t.num
and tmp.field1=t.field1
and  tmp.field3=t.field3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources