How to filter find duplicate and show row with same field using sqlite code?

(1) By monkelvin (kelvimchop) on 2021-09-30 09:56:22 [link] [source]

Hello, 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 the same name but different person_no ?

Sorry if my question bothers you

(2) By monkelvin (kelvimchop) on 2021-09-30 10:54:08 in reply to 1 [link] [source]

My apologies. The expected result is to list all the personal detail of everyone who shares the same name with someone –

(3) By Gunter Hick (gunter_hick) on 2021-09-30 11:20:28 in reply to 1 [link] [source]

What you are looking for is an "autojoin", i.e. joining a table to itself.

SELECT a.* from person a join person b on (a.name = b.name and a.person_no <> b.person_no);

(4.1) By Gerry Snyder (GSnyder) on 2021-09-30 22:39:56 edited from 4.0 in reply to 1 [link] [source]

Another possibility:

sqlite> create table a(id,name);
sqlite> insert into a values (1,'john'), (2,'john'), (3,'ann'), (4,'pete');
sqlite> select * from a where name in (select name from a group by name having count(*) > 1);

1|john
2|john

Gerry

(5) By Ryan Smith (cuz) on 2021-09-30 20:54:45 in reply to 1 [link] [source]

To add to the other excellent replies, a favourite of mine if I want to see how many and which records are duplicate, is to do:

SELECT name, COUNT(*) AS cnt, GROUP_CONCAT(person_no) AS nos
  FROM table
 GROUP BY name
 HAVING COUNT(*) > 1

(6.5) By Marco Bubke (marcob) on 2021-09-30 21:52:12 edited from 6.4 in reply to 1 [source]

You can use a window function too. Maybe somebody finds an even better solution
for window functions.

SELECT person_no, name, birthday
FROM (
  SELECT 
    person_no,
    name,
    birthday,
    count(name) OVER (
      PARTITION BY name
    ) counter
  FROM
    person
)
WHERE counter > 1

or 

WITH counted_persons AS (
  SELECT 
    *,
    count(name) OVER (
	    PARTITION BY name
    ) counter
  FROM
    person)
SELECT person_no, name, birthday FROM counted_persons WHERE counter > 1