Keep first and remove dupliciate rows only using sqlite

Question

Maybe i should do this in C# but i have more then one row with linkId X. I would like to remove it but i am unsure how. In code i could just use a foreach from 0 to n and remove any found rows with a greater (or !=) id but thats in code. Is there a less difficult way of doing it using sqlite?

Answer 1

Assuming the table's name is tableName and there is a primary key field named id, the following sql would do it. I think the following SQL query is general enough and should be able to be executed under any database engine.

delete from tableName
where id not in (
    select min(id) from tableName
    group by linkId
)