Re: Re: SELECTing a row by maximum value

Sorry, I forgot to add the group by clause. Both will work fine. "Marek Kilimajer" <kilimajer@webglobe.sk> wrote in message news:3E109FD1.7030503@webglobe.sk... > You cannot mix grouping columns without a group by clause. This would work: > > SELECT * FROM MYTABLE order by ifield desc limit 1 > > > > Elna Moorhouse wrote: > > >TRY: > >SELECT id,Max(ifield) from MYTABLE. > > > >What I don't understand is why you specify id=$id in the where clause. If > >you already specify the id as let say 1 then the max for that id will be 100 > >from your example below. > > > > > >"Richard Fox" <rfox@sbsii.com> wrote in message > >news:003901c2b034$0c563740$0700a8c0@mygroup... > >Hi, > > > >What would be a query to select a single record from a number of records in > >a table, depending on which one has the biggest value in an integer field? > > > >e.g something like: > > > >MYTABLE > >------------ > >id ifield > >1 100 > >2 200 > >3 150 > > > >SELECT * FROM MYTABLE WHERE id=$id and MAX(ifield) > > > >should return id=2, ifield=200 > > > >(but this doesn't work, of course) > > > >Many thanks, > > > >Richard > > > > > > > > > > >