Delete maxdate From table without ID

Question

I want to Delete a row with the highest date, but the table I am working with doesn't have auto increment ID's instead it has multiple rows with the same ID, but each row has different information.

I want to DELETE the highest date, because that is also the last date that is inserted with that ID so that is why I want to use MAX, otherwise I am deleteing all the rows with that ID.

Right now I am trying (This is my query from my PHP file):

DELETE FROM onderhoudsLog 
WHERE systeemContractID = :systeemContractID 
AND startDatum = MAX(:startDatum)

The DELETE does not work.

This is my PHP function:

function onderhoud($id, $startDatum){
  $query = "DELETE FROM onderhoudsLog WHERE systeemContractID = :id AND startDatum = MAX(:startDatum");
  $q = $this->DB->prepare($query);
  $q->execute(Array(
    ':id' => $id,
    ':startDatum' => $startDatum,
  ));
}

That looks like a query you are using in PHP. So please show the PHP as well as the raw query. The query may be fine but the way you bind the parameters is probably wrong — RiggsFolly, Feb 05 '18 at 14:07
Maybe you should start by reading the manual for the [MAX() function](https://dev.mysql.com/doc/refman/5.7/en/example-maximum-column.html) — RiggsFolly, Feb 05 '18 at 14:12

score 2 · Accepted Answer · answered Feb 05 '18 at 14:08

2

Order the data by date and delete only the first record

DELETE FROM onderhoudsLog 
WHERE systeemContractID = :systeemContractID 
ORDER BY startDatum DESC
LIMIT 1

answered Feb 05 '18 at 14:08

juergen d

201,996
37
293
362

Thank you for this answers! Is does work now :)!!! Thank you verry much. Sorry but I can't make this "the best comment" because I don't have enough reputation yet :( – Yannick Gijsbers Feb 05 '18 at 14:13
@YannickGijsbers i advice you to accept answers ( https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work ) if they work.. you already have a unaccepted question streak – Raymond Nijland Feb 05 '18 at 14:19
@RaymondNijland Thanks for telling me! I did it right away ! – Yannick Gijsbers Feb 05 '18 at 15:41

Delete maxdate From table without ID

1 Answers1