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

avatar image

hari
53 2 2 6

 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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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

avatar image

WilliamD
26.2k 18 33 48

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.

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:

x13

asked: Jan 07, 2011 at 06:22 AM

Seen: 1059 times

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

Copyright 2016 Redgate Software. Privacy Policy