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 <vikkiatb...@yahoo.in> 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.ka...@education.lu> > To: armando <armand...@gmail.com> > Cc: tech list <bluetm...@gmail.com>; 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.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 <bluetm...@gmail.com> > > > > > >> 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=armand...@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