CODEWITHSUNDEEP
sqlsql-serverselect
Pikachu620
Pikachu620

Reputation: 483

SQL Server, Find duplicate rows base on a column

Say if I have a datatable as follow:

  ID        Name         Birthday
------    --------     ------------
  01        Eric          06/20
  02        Judy          03/15
  03        Mary          04/01
  04        John          03/15
  05        Judy          06/20
  06        John          09/11

How can I get the result base on Name:

  ID        Name         Birthday
------    --------     ------------
  02        Judy          03/15
  04        John          03/15
  05        Judy          06/20
  06        John          09/11

Because both Judy and John appeared more than once (duplicates).

Base on Birthday:

  ID        Name         Birthday
------    --------     ------------
  01        Eric          06/20
  02        Judy          03/15
  04        John          03/15
  05        Judy          06/20

Because both 06/20 and 03/15 appeared more than once!

Upvotes: 2

Views: 56

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Why not use exists ?

select * 
from table t
where exists (select 1 
              from table 
              where Name = t.name 
              having count(1) > 1
             );

In order to get date based on Birthday, use Birthday column in inner query.

Upvotes: 1

Jeffry Evan
Jeffry Evan

Reputation: 327

based on Name

select * from myTable where name in(
select Name from myTable group by Name having count(*)>1)

you can make small change on that for birtday

this is the SQL Fiddle link where you can change the table Name or field data type based on your real DB

SQL Fiddle Link

Upvotes: 2

DhruvJoshi
DhruvJoshi

Reputation: 17126

You should look up WHERE, GROUP BY clauses as well as INNER queries and JOINs in SQL server. Here are the link to MSDN documentation

https://learn.microsoft.com/en-us/sql/t-sql/queries/where-transact-sql

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017

Using GROUP BY and WHERE your query for case 1 should be:

Select * from Tbl where Name in 
(
Select Name
from Tbl
Group by Name
HAVING COUNT(1)>1
)

Similarly second result should be like:

Select * from Tbl where Birthday in 
(
Select Birthday
from Tbl
Group by Birthday
HAVING COUNT(1)>1
)

Upvotes: 2

Related Questions