Find duplicate column multiple value in db

Question

I have a sqlite db with the following columns:

id   home   path    name    layer
1   /home   test    user1   1
2   /etc    test2   user2   1
3   /home   test    user3   1
4   /home   test    user4   1
5   /home   test    user1   1
6   /etc    test2   user2   1

How can I delete all duplicates, if home, path and name be equal, but only if all 3 be the same?

Sl id 1 should be deleted because its a duplicate of id 5 and id 2 delete because it's a duplicate of id 6.

This is how it should look like:

id   home   path    name    layer
3   /home   test    user3   1
4   /home   test    user4   1
5   /home   test    user1   1
6   /etc    test2   user2   1

Answer 1

You can group by the columns home, path, name and select MAX(id).
In this case SQLite will return for each combination of the 3 columns only 1 row which is the one with the max id:

SELECT MAX(id) id, home, path, name, layer
FROM tablename
GROUP BY home, path, name
ORDER BY id

This is a documented feature of SQLite.

If you want to delete the duplicates:

DELETE FROM tablename
WHERE id NOT IN (SELECT MAX(id) FROM tablename GROUP BY home, path, name)

See the demo.
Results:

id	home	path	name	layer
3	/home	test	user3	1
4	/home	test	user4	1
5	/home	test	user1	1
6	/etc	test2	user2	1