I have database like this (Example)

Where client has two types of photos, and i need to delete 1 type of photo(license or ident) by the date column(the oldest one). For example for client_id 1 i need to delete "ident", and for client 2 delete "license" I need to use this process for a large amount of data Please could you provide sollution for this process.

CodePudding user response：

Here's one option: find rowids for rows you'd want to delete, and then ... well, delete them:

Sample data:

SQL>   SELECT *
  2      FROM test
  3  ORDER BY client_id, datum;

 CLIENT_ID PHOTO_T DATUM
---------- ------- ----------
         1 ident   12.10.2022
         1 license 13.10.2022
         2 license 14.10.2022
         2 ident   15.10.2022
         3 license 15.10.2022
         4 ident   16.10.2022

6 rows selected.

Delete:

SQL> DELETE FROM
  2     test a
  3        WHERE a.ROWID IN
  4                 (SELECT x.rid
  5                    FROM (SELECT ROWID rid,
  6                                 b.client_id,
  7                                 ROW_NUMBER ()
  8                                    OVER (PARTITION BY b.client_id
  9                                          ORDER BY b.datum DESC) rn
 10                            FROM test b) x
 11                   WHERE     x.client_id = a.client_id
 12                         AND x.rn > 1);

2 rows deleted.

Result:

SQL>   SELECT *
  2      FROM test
  3  ORDER BY client_id, datum;

 CLIENT_ID PHOTO_T DATUM
---------- ------- ----------
         1 license 13.10.2022
         2 ident   15.10.2022
         3 license 15.10.2022
         4 ident   16.10.2022

SQL>

CodePudding user response：

What about this?

DELETE FROM table a
WHERE EXISTS (
 SELECT 1 FROM table b
 WHERE b.client_id = a.client_id
 AND   b.date > a.date
)

CodePudding user response：

DELETE is such a costy operation especially for large data sets. Rather using a CreateTableAS statement might be preferred to quickly manage the same issue such as

CREATE TABLE t1 AS
SELECT client_id, photo_type, "date"
  FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY "date" DESC) AS rn
          FROM t)
 WHERE rn = 1

where the query only picks single row per each client_id even if ties occur for "date" values.

Demo