Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
show duplicate rows in sql server

Answered show duplicate rows in sql server

  • Wednesday, January 09, 2013 2:48 PM
     
     
    Sign In to Vote
    0

    consider,

    create table testing
    (
    id int,
    col varchar(199)
    )

    insert into testing select 1,'a'
    insert into testing select 1,'b'
    insert into testing select 2,'a'
    insert into testing select 3,'c'
    insert into testing select 4,'d'
    insert into testing select 4,'s'

    I want to display only records which have duplicate values say the output should show


    1,'a'
    1,'b'
    4,'d'
    4,'s'


    Thanks
    Rakesh.

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

     

All Replies

  • Wednesday, January 09, 2013 2:53 PM
     
     Answered Has Code
    Sign In to Vote
    0
     

    SELECT * from testing where ID in (Select id FROM testing GROUP BY ID HAVING COUNT(*) > 1)

    Chuck Pedretti | Magenic – North Region | magenic.com



    • Edited by Chuck Pedretti Wednesday, January 09, 2013 2:55 PM
    • Proposed As Answer by Barry Marshall Wednesday, January 09, 2013 4:02 PM
    • Marked As Answer by Iam_Rakesh Thursday, January 10, 2013 12:04 PM
    •  
     
  • Wednesday, January 09, 2013 2:54 PM
     
     Answered Has Code
    Sign In to Vote
    1

    Try this:

    declare @testing table 
(
id int,
col varchar(199)
)

insert into @testing select 1,'a'
insert into @testing select 1,'b'
insert into @testing select 2,'a'
insert into @testing select 3,'c'
insert into @testing select 4,'d'
insert into @testing select 4,'s'


--select * from @testing 

;with cte(id) as( 	select id from @testing group by id having count( id ) > 1 )

select t.* from @testing t inner join cte c on t.id = c.id

    regards !

    Sergio Sánchez Arias

    • Proposed As Answer by Barry Marshall Wednesday, January 09, 2013 4:02 PM
    • Marked As Answer by Iam_Rakesh Thursday, January 10, 2013 12:04 PM
    •  
     
  • Wednesday, January 09, 2013 4:27 PM
     
      Has Code
    Sign In to Vote
    0
     
    ;WITH CteDup AS 
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS RN
FROM @testing
)
SELECT a.* FROM @testing AS a
INNER JOIN CteDup AS b
ON a.id = b.id 
AND RN>1

    Narsimha

     
  • Wednesday, January 09, 2013 4:51 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0
     

    ;with cte as (select *, count(Col) over (partition by Id) as cntDups from Testing)
select * from cte where cntDups > 1

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog