MySQL: DELETE + ORDER BY

locu

I need to delete the oldest row in the table...

I tried:
"DELETE FROM table_name ORDER BY colum_name LIMIT 1"
but I get this error:
"You have an error in your SQL syntax near 'ORDER BY colum_name LIMIT 1' at line 1"

while
"DELETE FROM table_name LIMIT 1"
does work but deletes the newest row...

Why doesn't this work? The MySQL-manual indicates that the first query is correct.

cgraz

From mysql.com:

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

Their query works just fine using the ORDER BY and LIMIT. Maybe your problem is something else. I'm not sure on this, but maybe if you use ORDER BY you need to use WHERE along with it. I'm not positive; it would require some playing around with. Are you using mysql_error() when you execute your query?

$query = mysql_query("DELETE FROM table_name WHERE id != 'a' ORDER BY column_name LIMIT 1") or die(mysql_error());

I added the WHERE (you can try without it). Your id field would be numbers, so they would never equal 'a'. This is just incase you do need the WHERE clause along with it, but you may be able to get away with not using it. Just make sure to replace table_name and column_name with their respective entries. mysql_error() will give you a more descriptive error message.

Cgraz

locu

I tried it with a WHERE clause in it. I used "WHERE 1>0".

I did use mysql_error(), that is why I know ORDER BY is the problem.

Could the configuration of MySQL itself be the problem... Is there some setting that for some reason makes this query inpossible?

cgraz

i'm not to sure. I've never used a DELETE like this. What did mysql_error() print, and can you copy and paste your exact code?

Cgraz

locu

Code:
###########
require("../../common.php");
mysql_connect($ps_mysql_adres,$ps_mysql_user,$ps_mysql_pw);
mysql_select_db($ps_mysql_db);

if (mysql_result(mysql_query("SELECT COUNT(*) FROM ps_gb"),0,0)>10) {
mysql_query("DELETE FROM ps_gb WHERE 1>0 ORDER BY naam LIMIT 1");
}
echo mysql_error();
###########
MySQL_error_code:
###########
You have an error in your SQL syntax near 'ORDER BY naam LIMIT 1' at line 1
###########

cgraz

hmmm. For now, why not just run 2 queries. One to get the id you want to delete, the 2nd one to delete it from the databse. I've never tried doing this before; it would just take some playing around with.

Cgraz

toma42

get rid of your order by. that's a horrible way tto try to specify rows to delete. Use the primary keys instead. NEVER use an order by in a delete statement....it's horrible coding. Always put filters for rows to delete into your where clause.

locu

What would be a good way to delete the row with the latest date?

locu

The weird part of the story is that the MySQL-manual says:
##########
DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

This will delete the oldest entry (by timestamp) where the row matches the WHERE clause.
##########