Search code examples
c++sqlsqlitesubquerysql-delete

Sqlite Query to remove duplicates from one column. Removal depends on the second column

Please have a look at the following data example:

enter image description here

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?

Solution

  • Your table has rowid as primary key.
    Use it to get the rowids 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.