Removing Duplicate Rows based on

Question

I have such a database structure :

CREATE TABLE `tenant_counter` (
    `id`    TEXT,
    `date`  TEXT,
    `time`  TEXT,
    `date_time` TEXT,
    `sayacdeger`    REAL,
    PRIMARY KEY(id,date_time)
);

In some rows, id, date and sayacdeger occurs multiple times with all keeping their values. So I need to keep only one of them. I tried :

delete from tenant_counter 
where exists (
    select 1 from tenant_counter r
    where r.id = tenant_counter.id
    and r.date = tenant_counter.date
    and r.sayacdeger = tenant_counter.sayacdeger
)

But that deletes all duplicated rows.

Possible duplicate of [Deleting duplicate rows from sqlite database](https://stackoverflow.com/q/8190541/608639) — jww, Nov 02 '19 at 02:51

score 1 · Answer 1 · answered Aug 11 '15 at 11:40

You can use ROWID , and only leave those rows , which has the lowest ( min ) ROWID for given data

like this

delete from tenant_counter 
where exists (
    select 1 from tenant_counter r
    where r.id = tenant_counter.id
    and r.date = tenant_counter.date
    and r.sayacdeger = tenant_counter.sayacdeger
    and r.rowid < tenant_counter.rowid
)

with IN approach

DELETE FROM tenant_counter 
WHERE ROWID NOT IN ( 
    select min(rowid) from tenant_counter r 
    group by r.id,r.date,r.sayacdeger 
)

Can it be done with min(timestamp) as well? – Elad Benda Sep 22 '21 at 00:30 — Elad Benda, Sep 22 '21 at 00:30

score 0 · Answer 2 · answered Aug 11 '15 at 11:41

delete   from tenant_counter
where    rowid not in
         (
         select  min(rowid)
         from    tenant_counter AS r
         where r.id = tenant_counter.id
         and r.date = tenant_counter..date
         and r.sayacdeger = tenant_counter.sayacdeger

)

Removing Duplicate Rows based on

2 Answers2