max() can"t work

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=gcdmg-mysql-2@m.gmane.org

Re: max() can"t work

--0016e6d7ea69ca12f2047eef8c31
Content-Type: text/plain; charset=ISO-8859-1

the field "movid" is type integer or varchar ?


2010/2/6 tech list

> 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=armandfp1@gmail.com
>
>


--
================================
ing. paredes aguilar, armando
http://www.sinapsisperu.com/
Desarrollador

--0016e6d7ea69ca12f2047eef8c31--

Re: max() can"t work

--------------090008080105050302060109
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.ht ml . 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
>
>
>> 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=armandfp1@gmail.com
>>
>>
>>
>
>
>

--------------090008080105050302060109--

Re: max() can"t work

--0016e6d785539cb45b047ef061dc
Content-Type: text/plain; charset=ISO-8859-1

Yes - you must use the subselect. Or, you can set a variable like:

select @max := max(movid) from table_name;
select * from table_name where movid = @max;


On Sat, Feb 6, 2010 at 8:34 AM, tech list wrote:

> 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=jlyons4435@gmail.com
>
>


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

--0016e6d785539cb45b047ef061dc--

Re: max() can"t work

--0-2062086622-1265475693=:16502
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

hi It is not working, select * from table_name group by movid hav=
ing max(movid) but it is working fine select * from table_name or=
der by movid desc limit 1 ________________________________=
=0AFrom: Roland Kaber =0ATo: armando gmail.com>=0ACc: tech list ; mysql@lists.mysql.com=0AS=
ent: Sat, 6 February, 2010 8:28:06 PM=0ASubject: Re: max() can't work =
The max() function is an aggregate function which can be used in =0Aconjunc=
tion with GROUP BY in the SELECT or HAVING clause: =0Ahttp://dev.mysql.com/=
doc/refman/5.0/en/group-by-functions.html . This =0Acode should work:=0Asel=
ect * from table_name group by movid having max(movid). However, there=
is a simpler and more efficient solution:=0Aselect * from table_name order=
by movid desc limit 1. I hope this helps. Best regards=0ARoland =
Kaber armando wrote:=0A> the field "movid" is type integer or varchar =
?=0A>=0A>=0A> 2010/2/6 tech list =0A>=0A> =0A>> selec=
t * from table_name where movid =3D max(movid);=0A>>=0A>> why the sql above=
can't work?=0A>> Shall I use a sub-select instead?=0A>>=0A>> select * from=
table_name where movid =3D (select max(movid) from table_name)=0A>> ?=0A>>=
=0A>>=0A>> Thanks in advance.=0A>>=0A>> --=0A>> MySQL General Mailing List=
=0A>> For list archives: http://lists.mysql.com/mysql=0A>> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Darmandfp1@gmail.com=0A> >=0A>>=0A>> =
=0A>=0A>=0A> The INTERNET now has a personality. YOUR=
S! See your Yahoo! Homepage. http://in.yahoo.com/
--0-2062086622-1265475693=:16502--

Re: max() can"t work

--0016e6d7e74411d025047ef1ffd5
Content-Type: text/plain; charset=ISO-8859-1

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 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
> To: armando
> Cc: tech list ; mysql@lists.mysql.com
> 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.ht ml . 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
> >
> >
> >> 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=armandfp1@gmail.com
> >>
> >>
> >>
> >
> >
> >
>
>
>
> 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

--0016e6d7e74411d025047ef1ffd5--