Search code examples
sqlsql-delete

How can I get my SQL query to delete the most recent record?

I'm trying to delete the most recent record where the job_id is equal to the id from jobs where the status is paused and know of no such functionality. Is there a way to do this?

DELETE FROM
  records
WHERE
  **due_date = most recent**
  AND job_id IN (
    SELECT
      id
    FROM
      jobs
    WHERE
      status = 'paused'
  )
Solution

  • Try the below query.

    DELETE FROM records WHERE due_date IN 
( SELECT due_date FROM records ORDER BY due_date DESC LIMIT 1) 
and job_id IN (SELECT id FROM jobs WHERE status = 'paused') ;

    I am assuming below table schema, Please correct if otherwise

    CREATE TABLE records(Id integer PRIMARY KEY, due_date date,name varchar2,job_id integer);
CREATE TABLE jobs(Id integer PRIMARY KEY, status varchar2);


INSERT INTO records VALUES(1,'17/12/2015','1','3');
INSERT INTO records VALUES(2,'17/12/2020','2','3');

INSERT INTO jobs VALUES(3,'paused');
INSERT INTO jobs VALUES(4,'paused');
COMMIT;