CODEWITHSUNDEEP
mysqlsqlsubqueryinner-joinhaving-clause
Omega Zero
Omega Zero

Reputation: 41

How to display all duplicate value

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    |

Upvotes: 0

Views: 36

Answers (1)

GMB
GMB

Reputation: 222582

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

Upvotes: 1

Related Questions