Please have a look at the following data example:
In this table, I have multiple columns. There is no PRIMARY KEY, as per the image I attached, there are a few duplicates in STK_CODE. Depending on the (min) column, I want to remove duplicate rows.
According to the image, one stk_code has three different rows. Corresponding to these duplicate stk_codes, value in (min) column is different, I want to keep the row which has minimum value in (min) column.
I am very new at sqlite and I am dealing with (-lsqlite3) to join cpp with sqlite.
Is there any way possible?
Advertisement
Answer
Your table has rowid
as primary key.
Use it to get the rowid
s that you don’t want to delete:
DELETE FROM comparison WHERE rowid NOT IN ( SELECT rowid FROM comparison GROUP BY STK_CODE HAVING (COUNT(*) = 1 OR MIN(CASE WHEN min > 0 THEN min END)) )
This code uses rowid
as a bare column and a documented feature of SQLite with which when you use MIN()
or MAX()
aggregate functions the query returns that row which contains the min or max value.
See a simplified demo.