Finding duplicate values in a SQL table

Question

It's easy to find duplicates with one field:

SELECT name, COUNT(email) 
FROM users
GROUP BY email
HAVING COUNT(email) > 1

So if we have a table

ID   NAME   EMAIL
1    John   [e-mail]
2    Sam    [e-mail]
3    Tom    [e-mail]
4    Bob    [e-mail]
5    Tom    [e-mail]

This query will give us John, Sam, Tom, Tom because they all have the same email.

However, what I want is to get duplicates with the same email and name.

That is, I want to get "Tom", "Tom".

The reason I need this: I made a mistake, and allowed to insert duplicate name and email values. Now I need to remove/change the duplicates, so I need to find them first.

Answer 1

If you wish to see if there is any duplicate rows in your table, I used below Query:

create table my_table(id int, name varchar(100), email varchar(100));

insert into my_table values (1, 'shekh', '[e-mail]');
insert into my_table values (1, 'shekh', '[e-mail]');
insert into my_table values (2, 'Aman', '[e-mail]');
insert into my_table values (3, 'Tom', '[e-mail]');
insert into my_table values (4, 'Raj', '[e-mail]');


Select COUNT(1) As Total_Rows from my_table 
Select Count(1) As Distinct_Rows from ( Select Distinct * from my_table) abc 

Answer 2

How we can count the duplicated values?? either it is repeated 2 times or greater than 2. just count them, not group wise.

as simple as

select COUNT(distinct col_01) from Table_01

Answer 3

SELECT * FROM users u where rowid = (select max(rowid) from users u1 where
u.email=u1.email);

Answer 4

try this code

WITH CTE AS

( SELECT Id, Name, Age, Comments, RN = ROW_NUMBER()OVER(PARTITION BY Name,Age ORDER BY ccn)
FROM ccnmaster )
select * from CTE 

Answer 5

 SELECT name, email 
    FROM users
    WHERE email in
    (SELECT email FROM users
    GROUP BY email 
    HAVING COUNT(*)>1)

Answer 6

SELECT id, COUNT(id) FROM table1 GROUP BY id HAVING COUNT(id)>1;

I think this will work properly to search repeated values in a particular column.