Organizational Research By

Surprising Reserch Topic

find duplicate entries in a column,ind,duplicate,CTN,Records,in,table1


find duplicate entries in a column  using -'I,am,writing,this,query,to,find,duplicate,CTN,Records,in,table1.,So,my,thinking,is,if,the,CTN_NO,appears,more,than,twice,or,higher,,,I,want,it,shown,in,my,SELECT,*,statement,output,on,top.

I,tried,the,following,sub-query,logic,but,I,need,pulls,

,,SELECT,*
,,,,,,,,,table1,
,,,WHERE,S_IND='Y'
,,,,,and,CTN_NO,=,(select,CTN_NO,
,,,,,,,,,,,,,,,,,,,,,from,table1,
,,,,,,,,,,,,,,,,,,,,where,S_IND='Y',
,,,,,,,,,,,,,,,,,,,,,,and,count(CTN_NO),<,2);
order,by,2'

    This question already has an answer here:
    
        
            How do I find duplicate values in a table in Oracle?
                
                    8 answers

asked Sep 24, 2015 by shikhar jain
edited Nov 18, 2015 by rajesh
0 votes
15 views



Related Hot Questions

2 Answers

0 votes

Using:

  SELECT t.ctn_no
    FROM YOUR_TABLE t
GROUP BY t.ctn_no
  HAVING COUNT(t.ctn_no) > 1

...will show you the ctn_no value(s) that have duplicates in your table. Adding criteria to the WHERE will allow you to further tune what duplicates there are:

  SELECT t.ctn_no
    FROM YOUR_TABLE t
   WHERE t.s_ind = 'Y'
GROUP BY t.ctn_no
  HAVING COUNT(t.ctn_no) > 1

If you want to see the other column values associated with the duplicate, you'll want to use a self join:

SELECT x.*
  FROM YOUR_TABLE x
  JOIN (SELECT t.ctn_no
          FROM YOUR_TABLE t
      GROUP BY t.ctn_no
        HAVING COUNT(t.ctn_no) > 1) y ON y.ctn_no = x.ctn_no
answered Sep 24, 2015 by patelnikul321
0 votes

Try this query.. It uses the Analytic function SUM:

SELECT * FROM
(  
 SELECT SUM(1) OVER(PARTITION BY ctn_no) cnt, A.*
 FROM table1 a 
 WHERE s_ind ='Y'   
)
WHERE cnt > 2

Am not sure why you are identifying a record as a duplicate if the ctn_no repeats more than 2 times. FOr me it repeats more than once it is a duplicate. In this case change the las part of the query to WHERE cnt > 1

answered Sep 24, 2015 by sujata naik

...