Deleting a row based on the max value

Question

How can I structure a mySQL query to delete a row based on the max value.

I tried

WHERE jobPositonId = max(jobPostionId)

but got an error?

Answer 1

Use:

DELETE FROM TABLE t1 
       JOIN (SELECT MAX(jobPositonId) AS max_id FROM TABLE) t2 
 WHERE t1.jobPositonId  = t2.max_id

Mind that all the rows with that jobPositonId value will be removed, if there are duplicates.

The stupid part about the 1093 error is that you can get around it by placing a subquery between the self reference:

DELETE FROM TABLE
 WHERE jobPositonId = (SELECT x.id
                         FROM (SELECT MAX(t.jobPostionId) AS id 
                                 FROM TABLE t) x)

Explanation

MySQL is only checking, when using UPDATE & DELETE statements, if the there's a first level subquery to the same table that is being updated. That's why putting it in a second level (or deeper) subquery alternative works. But it's only checking subqueries - the JOIN syntax is logically equivalent, but doesn't trigger the error.