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