che10234@hotmail.com wrote:
> 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

Use Google to search this group for the words "strawberry query" (include
the quotes).