Hi,

I've new to mysql and sql in general and I'm trying to find the
correct syntax to delete all rows EXCEPT the one row with the max
value of a certain field.

For example, my DB has:
UserName Sessions
Chris 2
Chris 4
Mark 4
Chris 3
Mark 2

I want to delete everything except:
Chris 4
Mark 4

So for each UserName, delete everthing except the row with the most
sessions.

Here's what I thought would work:


DELETE * from dbname as A where A.Sessions <> (select MAX(Sessions)
from dbname where UserName = A.UserName);

When I try this with mySql 4.1 I get:

Error Code : 1064
You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'select MAX(Sessions) from dbname where U
(0 ms taken)


Any ideas?!
Thanks