Find duplicate entries in a column [duplicate]

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 

Answer 1

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