List:General Discussion« Previous MessageNext Message »
From:Jim Lyons Date:February 6 2010 5:35pm
Subject:Re: max() can't work
View as plain text  
Why in the world would you think "select * from table_name group by movid
having max(movid)" would work? It seems to compile without errors but
doesn't give you what you seem to want.

This would work:

select * from table_name group by movid having movid = (select max(movid)
from table_name)

although then your' not really grouping so the GROUP BY is useless.

On Sat, Feb 6, 2010 at 11:01 AM, Vikram A <vikkiatbipl@stripped> wrote:

> hi
>
> It is not working,
>
> select * from table_name group by movid having max(movid)
>
> but it is working fine
>
> select * from table_name order by movid desc limit 1
>
>
>
>
>
> ________________________________
> From: Roland Kaber <roland.kaber@stripped>
> To: armando <armandfp1@stripped>
> Cc: tech list <BlueTMary@stripped>; mysql@stripped
> Sent: Sat, 6 February, 2010 8:28:06 PM
> Subject: Re: max() can't work
>
> The max() function is an aggregate function which can be used in
> conjunction with GROUP BY in the SELECT or HAVING clause:
> http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html . This
> code should work:
> select * from table_name group by movid having max(movid).
>
> However, there is a simpler and more efficient solution:
> select * from table_name order by movid desc limit 1.
>
> I hope this helps.
>
> Best regards
> Roland Kaber
>
> armando wrote:
> > the field "movid" is type integer or varchar ?
> >
> >
> > 2010/2/6 tech list <BlueTMary@stripped>
> >
> >
> >> select * from table_name where movid = max(movid);
> >>
> >> why the sql above can't work?
> >> Shall I use a sub-select instead?
> >>
> >> select * from table_name where movid = (select max(movid) from
> table_name)
> >> ?
> >>
> >>
> >> Thanks in advance.
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> >>
> >>
> >>
> >
> >
> >
>
>
>
>       The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
> http://in.yahoo.com/




-- 
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

Thread
max() can't worktech list6 Feb
  • Re: max() can't workarmando6 Feb
    • Re: max() can't workRoland Kaber6 Feb
      • Re: max() can't workVikram A6 Feb
        • Re: max() can't workJim Lyons6 Feb
  • Re: max() can't workJim Lyons6 Feb