Deleting A Row Based On The Max Value

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?

Solution 1:

DELETEFROMtableORDERBY jobPositonId DESC LIMIT 1

Solution 2:

Use:

DELETEFROMTABLE t1 
       JOIN (SELECTMAX(jobPositonId) AS max_id FROMTABLE) 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:

DELETEFROMTABLEWHERE jobPositonId = (SELECT x.id
                         FROM (SELECTMAX(t.jobPostionId) AS id 
                                 FROMTABLE 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.

Solution 3:

DELETEFROM `table_name` WHERE jobPositonId = (selectmax(jobPostionId) from `table_name` limit 1)

OR

DELETEFROM `table_name` WHERE jobPositonId IN (selectmax(jobPostionId) from `table_name` limit 1)

Solution 4:

This works:

SELECT@lastid :=max(jobPositonId ) from t1; 
DELETEfrom t1 WHERE jobPositonId =@lastid ; 

Other than going to the database twice, is there anything wrong with this technique?