I am learning the SQL code and don't know how to find the duplicate row and show it out. Here is the table
| person_no | name | birthday |
|---|---|---|
| 01 | John | 01/01/2000 |
| 02 | John | 15/03/2000 |
| 03 | Marry | 21/06/2000 |
| 04 | Peter | 23/12/2000 |
| 05 | Jerry | 12/07/2000 |
The person_no is unique for everyone, but the name may be same, anyway to find the people with same name but different person_no ?
Sorry if my question bothers you
select * from person_table
join(
select name from person_table
group by name
having count(person_no) > 1
) temp on person_table.name = temp.name;
First, find out the duplicate names from the subquery. Then using an inner join, find the rows in the table that have the names from the subquery result.
Without join:
select * from person_table
where name in (
select name from person_table
group by name
having count(person_no) > 1
)
This should also give you the rows with duplicate values is the column name, without using join.
group byfunction you are asking about ? explained here