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
3 Answers
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.
Comments
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
Comments
SELECT * FROM TAB1
WHERE T_NO IN(SELECT T_NO FROM TAB1 GROUP BY T_NO
HAVING COUNT(1)>1 )
