CODEWITHSUNDEEP
sqloracle-database
user7757349
user7757349

Reputation:

SQL query to Get the records of duplicate records

I've one table like

tab1

t_no   t_nm   t_ct
 1      abc    NY
 2      pqr    CA
 3      lmn    DC
 3      lmn    DC

How to get the record having duplicate values like:

O/P- 

t_no   t_nm   t_ct
 3     lmn    DC
 3     lmn    DC

Upvotes: 0

Views: 44

Answers (3)

JitendraG
JitendraG

Reputation: 26

SELECT * FROM TAB1
WHERE T_NO IN(SELECT T_NO FROM TAB1 GROUP BY T_NO
HAVING COUNT(1)>1 )

Upvotes: 0

Esteban P.
Esteban P.

Reputation: 2809

this statement would solve it too:

SELECT data.*
  FROM tbl data
  INNER JOIN 
      (
        SELECT count(*) as rc
              ,t_no
              ,t_nm
              ,t_ct
          FROM tbl
      GROUP BY t_no, t_nm, t_ct
        HAVING COUNT(*) > 1
      ) dupl
    ON data.t_no = dupl.t_no
   AND data.t_nm = dupl.t_nm
   AND data.t_ct = dupl.t_ct

Just in case you want to delete duplicate records and just keep one of them:

;WITH cte
AS
(
SELECT *
      ,ROW_NUMBER() OVER (PARTITION BY t_no, t_nm, t_ct ORDER BY t_no, t_nm, t_ct )  AS RowNum
FROM tbl
) 
DELETE FROM cte WHERE RowNum > 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use window functions:

select t.*
from (select t.*, count(*) over (partition by t_no, t_nm, t_ct) as cnt
      from t
     ) t
where cnt > 1;

Perhaps the count is enough? Then an aggregation is simplest:

select t_no, t_nm, t_ct
from t
group by t_no, t_nm, t_ct
having count(*) > 1;

I'm not sure why returning multiple rows with the same value is useful.

Upvotes: 2

Related Questions