x

Identify Duplicated from for certain records only

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

more ▼

asked Jan 07, 2011 at 06:22 AM in Default

hari gravatar image

hari
53 2 2 4

CREATE TABLE Test (
 sid int identity(1,1),
 sname varchar(255),
 extnum varchar(255),
 fd bit
)

INSERT INTO Test(sname,extnum,fd) values('hhh',11111,1)
INSERT INTO Test(sname,extnum,fd) values('bbb',22222,0)
INSERT INTO Test(sname,extnum,fd) values('ccc',44444,1)
INSERT INTO Test(sname,extnum,fd) values('hh',11111,0)
INSERT INTO Test(sname,extnum,fd) values('bb',22222,0)

SELECT *FROM Test WHERE ExtNum IN (
SELECT T.ExtNum FROM Test T
INNER JOIN (
SELECT ExtNum FROM Test  WHERE FD = 1) FDTrue <br> ON T.ExtNum = FDTrue.ExtNum
Group By T.ExtNum
Having Count(T.ExtNum) > 1)
drop table Test
Jan 08, 2011 at 05:31 AM hari
SELECT  EXT_VENDOR_NUMBER + DATA_SOURCE AS EXT_DATA_SOURCE,
        V.VENDOR_ID,
        V.NAME
FROM    cvmsa.vendor v
WHERE   v.delete_Flag IS NULL
        AND v.ext_vendor_number + v.data_source IN 
        (SELECT ext_vendor_number + data_source
         FROM   cvmsa.vendor
         WHERE  delete_flag IS NULL
                AND ext_vendor_number + data_source IN 
                (SELECT ext_vendor_number + data_source
                 FROM   cvmsa.vendor
                 WHERE  delete_flag IS NULL
                        AND SVU_Final_Scrub = 'SVU_Final_Scrub')
         GROUP BY ext_vendor_number + data_source
         HAVING COUNT(ext_vendor_number + data_source) > 1)
ORDER BY ext_vendor_number + data_source,
v.SVU_Final_Scrub
Jan 08, 2011 at 05:38 AM hari
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
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.
more ▼

answered Jan 07, 2011 at 06:26 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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       
more ▼

answered Jan 07, 2011 at 06:33 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 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, 2011 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, 2011 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, 2011 at 04:03 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x10

asked: Jan 07, 2011 at 06:22 AM

Seen: 904 times

Last Updated: Jan 07, 2011 at 06:35 AM