Find duplicate entries in a column

Question

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

"more than twice or higher"? Surely duplicate records are those that appear more than **once**. — Johnsyweb, Dec 23 '10 at 21:10
Related: [*Finding duplicates on one column using select where in SQL Server 2008*](https://stackoverflow.com/a/13923387/3357935) — Stevoisiak, Mar 03 '20 at 17:10

score 131 · Accepted Answer · answered Dec 23 '10 at 21:05

131

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 Dec 23 '10 at 21:05

OMG Ponies

300,587
73
490
482

3

Adding the self join was a very useful addition. – secretwep Aug 20 '15 at 23:09
@OMGponies can you please explain what the join does? – cokedude Nov 24 '17 at 11:43
Wiithout the self join, you'll see the ctn_no column and its count. With the self join, you'll see all the other columns in YOUR_TABLE too. – Malcolm Crum Mar 24 '21 at 03:56

Chandu · Answer 2 · 2010-12-23T21:14:38.747

3

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

edited Dec 23 '10 at 21:14

answered Dec 23 '10 at 21:08

Chandu

74,913
16
123
127

Find duplicate entries in a column

2 Answers2

Linked