SQLite Forum

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

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 [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 [link] [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