SEO

#1 (**permalink**) 02-28-2008, 09:29 AM

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

#2 (**permalink**) 02-28-2008, 09:29 AM

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).

#3 (**permalink**) 02-28-2008, 09:29 AM

What an interesting response....I feel like I've been told some secret
code. I found it and I'll try it out, thanks. Although I still don't
see what is syntactically wrong with my first attempt.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

mysql, dreaded error 1064