Find Duplicate rows in sql

Question

I want to find duplicate if there is duplicate in whole row via SQL. How to find here 2 rows with all values having duplicates like

Data set

id employee_name     dept  Dept_name
1    Abhijit         10    Physics
1    Abhijit         10    Physics
2    Debjit          20    Chemis
3    partha          30    Maths

Result set

id employee_name     dept  Dept_name
1    Abhijit         10    Physics
1    Abhijit         10    Physics

Refer to this post. http://stackoverflow.com/questions/7694263/filtering-duplicates-in-a-table-without-a-primary-key — n0klu, Feb 03 '17 at 20:23

score 5 · Answer 1 · answered Feb 03 '17 at 15:47

5

You can use having count(*) >1 grouping by your cols

select id, employee_name ,    dept,  Dept_name
from my_table 
group by  id, employee_name ,    dept,  Dept_name
having count(*) > 1

answered Feb 03 '17 at 15:47

ScaisEdge

131,976
10
91
107

score 3 · Answer 2 · answered Feb 03 '17 at 15:47

3

Simple group by and count based filter in having clause

Select id, employee_name, dept, dept_name
From your_table
Group by id, employee_name, dept, dept_name
Having count(*) > 1;

answered Feb 03 '17 at 15:47

Gurwinder Singh

38,557
6
51
76

You probably have whitespace or non printing character in your string. Otherwise the above solution should work just fine. – Gurwinder Singh Feb 03 '17 at 16:08

score 0 · Answer 3 · answered Feb 03 '17 at 15:52

0

Self join

  SELECT a.* from employees a, employees b WHERE a.id = b.id and a.employee_name = b.employee_name and a.dept = b.dept;

dept_name probably it the same for the deps with the same id, so no need to include it in where clause

answered Feb 03 '17 at 15:52

Ion Cojocaru

2,583
15
16

I have 27 columns in my table.self-join is not possible – Abhijit Feb 03 '17 at 16:03
someone please help – Abhijit Feb 03 '17 at 16:05
You should use an id/discriminator column(s) which will be your primary key and guarantee that the two rows that have the same value in that column are indeed duplicates. If such a relation can be inferred from your data, then it's sufficient to join just on it. In this concrete example the `id` field looks like a good candidate. – Ion Cojocaru Feb 03 '17 at 16:09
can you imagine how much join conditions I have to write?there are 27 – Abhijit Feb 03 '17 at 16:10
can anyone tell me the answer? – Abhijit Feb 03 '17 at 16:38

Find Duplicate rows in sql

3 Answers3