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
2

2 Answers

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
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