[sql] Find duplicate entries in a column

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

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