how to find duplicate records in sql server?

Today, We want to share with you sql query to find duplicate rows in a database.In this post we will show you sql query to find duplicate rows in a table, hear for sql select duplicate rows based on one column we will give you demo and example for implement.In this post, we will learn about sql unique count and use distinct in sql with an example.

Find Duplicates From a Table in SQL Server

mysql> select * from Contacts;
+-------+----------+
| name  | mobile    |
+-------+----------+
| Kajal | 98256021 |
| Mital | 12345678 |
| Meera | 12341234 |
| Dev   | 11115485 |
| Kajal | 98256021 |
| Mital | 12345678 |
| Meera | 12341234 |
| Dev   | 11115485 |
| Kajal | 98256021 |
| Mital | 12345678 |
| Meera | 12341234 |
| Dev   | 11115485 |
| Kajal | 98256021 |
| Mital | 12345678 |
| Meera | 12341234 |
| Dev   | 11115485 |
| Dara  |  8965342 |
| Dara  |  6888342 |
+-------+----------+
18 rows in set (0.00 sec)

mysql> select name, count(name) from members group by name;
+-------+-------------+
| name  | count(name) |
+-------+-------------+
| Meera |           4 |
| Kajal |           4 |
| Mital |           4 |
| Dev   |           4 |
| Dara  |           2 |
+-------+-------------+
5 rows in set (0.00 sec)

mysql> select name, count(name) from members group by name, mobile;
+-------+-------------+
| name  | count(name) |
+-------+-------------+
| Meera |           4 |
| Kajal |           4 |
| Mital |           4 |
| Dev   |           4 |
| Dara  |           1 |
| Dara  |           1 |
+-------+-------------+
6 rows in set (0.00 sec)

mysql> select name, count(name) as total_list from members group by name, mobile having times>1;
+-------+-------+
| name  | times |
+-------+-------+
| Meera |     4 |
| Kajal |     4 |
| Mital |     4 |
| Dev   |     4 |
+-------+-------+
4 rows in set (0.00 sec)

How to find duplicated rows?

create table tamilrokers(id int not null primary key, upcomming_movie_date date not null);

insert into tamilrokers(id, upcomming_movie_date) values(1, '2021-10-08');
insert into tamilrokers(id, upcomming_movie_date) values(2, '2021-10-08');
insert into tamilrokers(id, upcomming_movie_date) values(3, '2021-10-09');

select * from tamilrokers;
+----+------------+
| id | movie_date |
+----+------------+
|  1 | 2021-10-08 |
|  2 | 2021-10-08 |
|  3 | 2021-10-09 |
+----+------------+

select upcomming_movie_date, count(*) from tamilrokers GROUP BY upcomming_movie_date;
+------------+----------+
| movie_date | count(*) |
+------------+----------+
| 2021-10-08 |        2 |
| 2021-10-09 |        1 |
+------------+----------+

select upcomming_movie_date, count(*) from tamilrokers group by upcomming_movie_date HAVING count(*) > 1;
+------------+----------+
| movie_date | count(*) |
+------------+----------+
| 2021-10-08 |        2 |
+------------+----------+

Finding duplicate values in a SQL table

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

How to print duplicate rows in a table?

using GROUP BY and HAVING condition

select NAME
from Person
group by NAME
having count(NAME) > 1;

How to Find Duplicate Rows in SQL?

SELECT  name,
 type,
FROM items
GROUP BY name, type
HAVING COUNT(id) >1;

Delete Duplicate Records in SQL Using group By

SELECT member_name, LastName, contact_number, COUNT(*) as CNT
FROM  Member
GROUP BY member_name, LastName, contact_number;
HAVING COUNT(*)  = 1

Delete Duplicates Records in SQL Using Self Join

SELECT member_name, member_address, gender, marital_status
from members a
WHERE NOT EXISTS (select 1
        from members b
        where b.member_name = a.member_name and
              b.member_address = a.member_address and
              b.gender = a.gender and
              b.create_date >= a.create_date)

Delete Duplicate Records in SQL Using Row_Number

WITH members (Col1, Col2, Col3, DuplicateCount)
AS
(
 SELECT Col1, Col2, Col3,
 ROW_NUMBER() OVER(PARTITION BY Col1, Col2,
      Col3 ORDER BY Col1) AS DuplicateCount
 FROM MyTable
) SELECT * from members Where DuplicateCount = 1

Finding duplicate values in one column

select email,
       count(*)
from dedup
group by email
having count(*) > 1;

select row_number() over (partition by email),
       name,
       email
from dedup;

select * from (
    select row_number() over (partition by email),
        name,
        email
    from dedup ) t 
where t.row_number < 2;

Finding Duplicates with SQL

here simple example for SQL To Find Duplicates

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

Find Rows that Occur Once

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

I hope you get an idea about sql query to find duplicate rows in a database.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.