SQLite how to remove all duplicated rows except one?

Question

I have a weird table call mytable, no column is primary.

  Name    |  Company  |  Position
 Michael     Google      Tester
 Michael     Google      Tester
 Michael     Google      Tester
 Peter       Facebook    Developer
 Peter       Facebook    Developer
 Peter       Facebook    Developer
 Peter       Facebook    Developer

What I want

  Name    |  Company  |  Position
 Michael     Google      Tester
 Peter       Facebook    Developer 

With some solutions I found with the same question here, they did not work. For example: DELETE FROM mytable WHERE Name NOT IN (SELECT MAX(Name) FROM mytable GROUP BY Company);

I should edit right in this table, use SQLite, no new table creation and no CTE. How can I do it?

Answer 1

You can choose to keep the min or max of rowid grouping by the 3 columns shown.

delete from myTable
where rowid not in (select min(rowid)
                    from myTable
                    group by name,company,position)

Answer 2

SELECT DISTINCT  Name,  Company,  Position 
FROM yourTableName

In case you want to delete "duplicate" rows you might write this:

yourTableName - should be your real database table

yourColumn - should be your real database column

DELETE FROM yourTableName
WHERE yourColumn NOT IN (
    SELECT yourColumn 
    FROM yourTableName
    WHERE yourColumn IS NOT NULL
)