locked
Selecting Dupicate Records based on Column RRS feed

  • Question

  • Hi:

    I have got table1(Col1,Col2,Col3)
    I want all records for which Col2 is having duplicate records

    Eg:
    Col1    Col2   Col2

    xx       3      ggg
    jjj      3      uuu
    fff      3     bbbb
    hhh      5     jjjj
    dfd      5     hhhh
    fvvg     5     qqqq

    I am using the below query but not able to get through

    SELECT Col1,Col2,Col3 FROM  Table1
    group by Col1,Col2,Col3
    HAVING Count(Col2)>1

    Can any one please help

    Wednesday, March 5, 2014 6:55 AM

Answers

  • Try the below

    Create Table table1(Col1 varchar(5),Col2 int,Col3 varchar(50))
    Insert into Table1 Values
    ('xx',3,'ggg'),
    ('jjj',3,'uuu'),
    ('fff',3,'bbbb'),
    ('hhh',5,'jjjj'),
    ('dfd',5,'hhhh'),
    ('fvvg',5,'qqqq'),
    ('fvvg',4,'qqqq')
    
    ;With cte as(
    
    Select *,Count(col2)Over(Partition by Col2) Rn From table1)
    
    Select * From cte where rn>1
    
    Drop table table1


    • Marked as answer by Fanny Liu Wednesday, March 12, 2014 12:06 PM
    Wednesday, March 5, 2014 7:02 AM
  • Hi

    try this

    CREATE TABLE #temp (
    	col1 VARCHAR(10)
    	,col2 VARCHAR(20)
    	,col3 VARCHAR(10)
    	)
    GO
    
    INSERT INTO #temp
    VALUES (
    	'xx'
    	,'3'
    	,'ggg'
    	)
    
    INSERT INTO #temp
    VALUES (
    	'jjj'
    	,'3'
    	,'uuu'
    	)
    
    INSERT INTO #temp
    VALUES (
    	'fff'
    	,'3'
    	,'bbbb'
    	)
    
    INSERT INTO #temp
    VALUES (
    	'hhh'
    	,'5'
    	,'jjjj'
    	)
    
    INSERT INTO #temp
    VALUES (
    	'dfd'
    	,'5'
    	,'hhhh'
    	)
    
    INSERT INTO #temp
    VALUES (
    	'fvvg'
    	,'5'
    	,'qqqq'
    	)
    INSERT INTO #temp
    VALUES (
    	'fvvg'
    	,'6'
    	,'qqqq'
    	)
    
    SELECT *
    FROM #temp
    GO
    
    WITH cte
    AS (
    	SELECT *
    		,Count(col2) OVER (PARTITION BY Col2) Rn
    	FROM #temp
    	)
    SELECT col1,col2,col3
    FROM cte
    WHERE rn > 1
    go
    DROP TABLE #temp
    Mark as answer if you find it useful


    Shridhar J Joshi Thanks a lot


    • Edited by Shridhar J Joshi Wednesday, March 5, 2014 7:14 AM
    • Marked as answer by Fanny Liu Wednesday, March 12, 2014 12:06 PM
    Wednesday, March 5, 2014 7:14 AM
  • Sudhakar,

    check if this helps:

    ;with cte
    as
    (
    	select *,row_number() over(partition by col2 order by col2,col1,col3) rnum
    	from tbl_name
    )
    select * from cte
    where rnum <> 1

    Edited:

    If yur looking to get all records of 3 and 5 (which are in duplicted list) then check this code:

    select * from table1 where col2 in(
    SELECT col2 FROM  Table1
    group by Col2
    HAVING Count(Col2)>1)



    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>


    • Edited by Jayakumaur (JK) Wednesday, March 5, 2014 7:17 AM new method
    • Marked as answer by Fanny Liu Wednesday, March 12, 2014 12:06 PM
    Wednesday, March 5, 2014 7:15 AM

All replies

  • Try the below

    Create Table table1(Col1 varchar(5),Col2 int,Col3 varchar(50))
    Insert into Table1 Values
    ('xx',3,'ggg'),
    ('jjj',3,'uuu'),
    ('fff',3,'bbbb'),
    ('hhh',5,'jjjj'),
    ('dfd',5,'hhhh'),
    ('fvvg',5,'qqqq'),
    ('fvvg',4,'qqqq')
    
    ;With cte as(
    
    Select *,Count(col2)Over(Partition by Col2) Rn From table1)
    
    Select * From cte where rn>1
    
    Drop table table1


    • Marked as answer by Fanny Liu Wednesday, March 12, 2014 12:06 PM
    Wednesday, March 5, 2014 7:02 AM
  • SELECT *
    FROM Table t
    WHERE EXISTS (SELECT 1
    FROM Table
    WHERE Col2 = t.Col2
    AND Col1 <> t.Col1)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, March 5, 2014 7:07 AM
  • Hi

    try this

    CREATE TABLE #temp (
    	col1 VARCHAR(10)
    	,col2 VARCHAR(20)
    	,col3 VARCHAR(10)
    	)
    GO
    
    INSERT INTO #temp
    VALUES (
    	'xx'
    	,'3'
    	,'ggg'
    	)
    
    INSERT INTO #temp
    VALUES (
    	'jjj'
    	,'3'
    	,'uuu'
    	)
    
    INSERT INTO #temp
    VALUES (
    	'fff'
    	,'3'
    	,'bbbb'
    	)
    
    INSERT INTO #temp
    VALUES (
    	'hhh'
    	,'5'
    	,'jjjj'
    	)
    
    INSERT INTO #temp
    VALUES (
    	'dfd'
    	,'5'
    	,'hhhh'
    	)
    
    INSERT INTO #temp
    VALUES (
    	'fvvg'
    	,'5'
    	,'qqqq'
    	)
    INSERT INTO #temp
    VALUES (
    	'fvvg'
    	,'6'
    	,'qqqq'
    	)
    
    SELECT *
    FROM #temp
    GO
    
    WITH cte
    AS (
    	SELECT *
    		,Count(col2) OVER (PARTITION BY Col2) Rn
    	FROM #temp
    	)
    SELECT col1,col2,col3
    FROM cte
    WHERE rn > 1
    go
    DROP TABLE #temp
    Mark as answer if you find it useful


    Shridhar J Joshi Thanks a lot


    • Edited by Shridhar J Joshi Wednesday, March 5, 2014 7:14 AM
    • Marked as answer by Fanny Liu Wednesday, March 12, 2014 12:06 PM
    Wednesday, March 5, 2014 7:14 AM
  • Sudhakar,

    check if this helps:

    ;with cte
    as
    (
    	select *,row_number() over(partition by col2 order by col2,col1,col3) rnum
    	from tbl_name
    )
    select * from cte
    where rnum <> 1

    Edited:

    If yur looking to get all records of 3 and 5 (which are in duplicted list) then check this code:

    select * from table1 where col2 in(
    SELECT col2 FROM  Table1
    group by Col2
    HAVING Count(Col2)>1)



    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>


    • Edited by Jayakumaur (JK) Wednesday, March 5, 2014 7:17 AM new method
    • Marked as answer by Fanny Liu Wednesday, March 12, 2014 12:06 PM
    Wednesday, March 5, 2014 7:15 AM
  • Thank you all.
    Thursday, March 6, 2014 8:26 AM