Is there anyway I could display all the duplicate values in mysql using group by and having
+---------+--------------+------------+--------+----------+----------+---------+
| ENumber | EmpName | Birthdate | Gender | Address | Salary | DNumber |
+---------+--------------+------------+--------+----------+----------+---------+
| E001 | GSInocencio | 1988-01-15 | F | Munoz | 18000.00 | D005 |
| E002 | EAVillanueva | 1988-04-20 | F | Munoz | 23000.00 | D003 |
| E003 | ALedesma | 1988-05-25 | M | CLSU | 21000.00 | D002 |
| E004 | APGamilla | 1991-10-15 | F | Maligaya | 25000.00 | D001 |
| E005 | ACTolentino | 1989-02-20 | F | Maligaya | 30000.00 | D002 |
| E006 | ANVillasoto | 1999-01-05 | M | CLSU | 15000.00 | D004 |
| E007 | JPPalada | 1997-01-10 | M | Munoz | 21000.00 | D001 |
| E008 | NTNicodemus | 1995-04-15 | F | Maligaya | 22000.00 | D003 |
+---------+--------------+------------+--------+----------+----------+---------+
I want to display all the duplicate value in DNumber
+---------+--------------+------------+--------+----------+----------+---------+
| ENumber | EmpName | Birthdate | Gender | Address | Salary | DNumber |
+---------+--------------+------------+--------+----------+----------+---------+
| E004 | APGamilla | 1991-10-15 | F | Maligaya | 25000.00 | D001 |
| E007 | JPPalada | 1997-01-10 | M | Munoz | 21000.00 | D001 |
| E003 | ALedesma | 1988-05-25 | M | CLSU | 21000.00 | D002 |
| E005 | ACTolentino | 1989-02-20 | F | Maligaya | 30000.00 | D002 |
| E002 | EAVillanueva | 1988-04-20 | F | Munoz | 23000.00 | D003 |
| E008 | NTNicodemus | 1995-04-15 | F | Maligaya | 22000.00 | D003 |
display all the duplicate values in mysql using group by and having
There is more than one way to do it, but if you want to use group by and having, then you can join the table with an aggregate subquery that identifies the duplicates, as follows:
select t.*
from mytable t
inner join (
select dnumber
from mytable
group by dnumber
having count(*) > 1
) x on x.dnumber = t.dnumber
order by t.dnumber, t.enumber