+12 votes
656 views
in Programming by Expert (5.9k points)
How to print duplicate entry from a table with their count? my table has junk data and want to see the duplicates values, can you tell me the exact query for the same?
closed

1 Answer

+4 votes
by (1.9k points)
selected by
 
Best answer

For print duplicate entry from a table with their count see this query ->

select * FROM

(SELECT ROW_NUMBER() OVER (PARTITION BY fname  ORDER BY fname) cnt,*

FROM USER_TABLE)  duplicates
 
WHERE duplicates.Cnt > 1
NOTE: Here  fname is column name and USER_TABLE is table in which you want to find duplicate entries.
0
by Expert (5.9k points)
yah thanks dear.
0
by (1.7k points)
Nice work @Eathen it helped me also.
0
by
Or you might use this query :

mysql>  select COLUMN_NAME_HERE, count(*) as c from TABLE_NAME_HERE group by COLUMN_NAME_HERE;

Not a Member yet?

Ask to Folks Login

My Account

Your feedback is highly appreciated