Find duplicate entries in a column [duplicate]
Using:
SELECT t.ctn_no FROM YOUR_TABLE tGROUP 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) > 1If 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
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 > 2Am 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