Re: help in getting results from mysql

On Tue, 3 Jul 2001 09:54, Christian Dechery wrote: > I have to get some results from a mysql table, and I'm pretty sure that > I'm not doing it the best way. > > its like this... I have a table (a historical one) with these fields: > date | item_id | item_type_id | qty | value > > and there are multiple lines for the pair item/type (as I said it's a > historical table). I want the row with the last date. > So if I had: > > 2001-29-6 | 34 | 3 | 5 | 15.0 > 2001-30-6 | 34 | 3 | 7 | 13.0 > 2001-1-7 | 34 | 3 | 9 | 12.0 > 2001-2-7 | 34 | 3 | 1 | 3.5 > > I only want the last one '2001-2-7'. But I can't think of a way of > doing this without sub-select. So I did this: > > <pre> > $rs1=mysql_query("select max(date) from tablename where item_id=34 and > item_type_id=3"); > $last_date=mysql_result($rs1,0,0); > > $rs2=mysql_query("select qty,value from tablename where > date='$last_date'"); list($qty,$value) = mysql_fetch_row($rs2); > </pre> > > it works fine... but I don't think it is good... whenever there is > 'code' between queries to get results I think something could be > optimized. Any ideas? Try $rs1=mysql_query("select max(date) AS latest, * from tablename where item_id=34 and item_type_id=3"); but consider that you may possibly have multiple entries for the latest date? -- David Robley Techno-JoaT, Web Maintainer, Mail List Admin, etc CENTRE FOR INJURY STUDIES Flinders University, SOUTH AUSTRALIA Tyre Shop sign - We Skid You Not.