Identify Duplicated from for certain records only

Question

There are duplicate records

Find duplicate records based on ExtNum.

But check that only for records where findduplicate= 'Y' EDIT (changed in sample to bit value)

DECLARE @TestTable TABLE
        (vid int,
         NAME varchar(20),
         extnum int,
         findduplicate bit)
    INSERT  INTO @TestTable
            (vid, NAME, extnum, findduplicate)
    VALUES  (1, 'hhh', 11111, 1),
            (2, 'bbb', 22222, 0),
            (3, 'ccc', 4444, 0),
            (4, 'hhh', 11111, 0),
            (5, 'bbb', 22222, 0) ;

So the result should return

VID          NAME         ExtNum
1            hhh           11111
4            hhh           11111

FYI:-
Though there are duplicates on the ExtgNum=22222, it should not come in the result set We should get only "11111" related supplier list

Answer 1

0

SELECT * FROM unspecified_table_name
WHERE EXTNUM IN 
(SELECT EXTNUM FROM unspecified_table_name GROUP BY EXTNUM HAVING COUNT(*) > 1
 INTERSECT
 SELECT EXTNUM FROM unspecified_table_name WHERE FINDDUPLICATE = 1)

I would have tested this for you too, if you had posted your table def and insert statements to specify your test case.

answered Jan 07 '11 at 06:26 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

(comments are locked)

Answer 2

0

I created the test data (and have appended it to your question

DECLARE @TestTable TABLE
    (vid int,
     NAME varchar(20),
     extnum int,
     findduplicate bit)
INSERT  INTO @TestTable
        (vid, NAME, extnum, findduplicate)
VALUES  (1, 'hhh', 11111, 1),
        (2, 'bbb', 22222, 0),
        (3, 'ccc', 4444, 0),
        (4, 'hhh', 11111, 0),
        (5, 'bbb', 22222, 0) ;
WITH    DupList
          AS (SELECT    *,
                        ROW_NUMBER() OVER (PARTITION BY extnum ORDER BY (SELECT NULL)) rn
              FROM      @testtable TT
              WHERE     EXISTS ( SELECT *
                                 FROM   @TestTable TT2
                                 WHERE  TT2.extnum = TT.extnum
                                        AND findduplicate = 1 ))
    SELECT  *
    FROM    DupList
    -- WHERE rn>1 -- Only return the entries that are duplicates

    /* Delete the Duplicates */
    --DELETE 
    --FROM    DupList
    --WHERE rn>1

answered Jan 07 '11 at 06:33 AM

WilliamD
25.3k ● 16 ● 18 ● 41

The exec plan is nicer here, but it just finds all rows where there is a matching extnum with findduplicate = 1 - whether there are duplicates or not... +1 for recreating the test data though :)

Jan 07 '11 at 06:58 AM Matt Whitfield ♦♦

Combine the answers to only show dupe rows and still provide a nice mechanism for deleting them?

WITH DupList
       AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY extnum ORDER BY (SELECT NULL)) rn
         FROM      @testtable TT
         WHERE     EXISTS ( 
          SELECT EXTNUM FROM @TestTable WHERE extnum = tt.extnum GROUP BY EXTNUM HAVING COUNT(*) > 1
              INTERSECT
          SELECT EXTNUM FROM @TestTable WHERE extnum = tt.extnum AND FINDDUPLICATE = 1))
    SELECT * FROM DupList;

:)

Jan 07 '11 at 11:39 AM KenJ

@Matt - the where clause that I commented out returns the ones that are duplicates, so it works - oramiwrong?

Jan 09 '11 at 04:03 AM WilliamD

(comments are locked)

Identify Duplicated from for certain records only

Follow this question

Related Questions