Search code examples
sqlsqlitesql-delete

Query to delete first added element by a topic when it exceeds provided limit

I have a table with 5 columns, one of the columns is topic. I need a query that will delete the row I added first (oldest) when I group it by topic.

I have an id column which is set as the primary key, however the increment(although it's all unique) is not reliable because of the way the data is getting filled with my python code.

This is for a SQLite database

DELETE FROM news where topic = "world"

The obvious issue with the above query is that it will delete everything when the topic = world, I only want to delete my oldest addition to the table

Solution

  • You can use a correlated subquery. You have specified the "oldest", so I assume there is some type of date/time column with this information:

    DELETE FROM news n
    WHERE n.topic = 'world' AND
          n.datetimecol = (SELECT MIN(n2.datetimecol)
                           FROM news n2
                           WHERE n2.topic = n.topic
                          );

    If not, you can use an auto-incremented primary key.