I think this does what you want. You can probably extend it to do the final check for val3 vs. val2 ....
select distinct t1.val1, max( t1.id ), t1.val2 from table as t1, table as t2 where t1.val2 <= t2.val2 group by t1.val1; HTH On Thursday 31 Jul 2003 3:22 pm, Petre Agenbag wrote: > Hi List > > I've been trying to do something with one MySQL query, but I don't think > it is possible, so, I would need to probably do multiple queries, and > possibly have queries inside loops, but I am rather weary of that so, > I'd like your views/possible solutions to doing the following: > > > Consider this: > > id val1 val2 val3 > 1 a 1 1 > 2 b 2 3 > 3 a 1 2 > 4 b 2 1 > 5 c 3 3 > 6 c 2 1 > > I need to query this table to return this: > > id val1 val2 val3 > 3 a 1 2 > 4 b 2 1 > 6 c 2 1 > > Thus, I need to firstly only return ONE row for each val1, and that row > MUST be that last row (ie, the row with the highest id, OR, should val3 > for instance be a date, then with the highest date). > > > if I do a > > select distinct val1, MAX(id) from table order by val1, then it returns > > id val1 > 3 a > 4 b > 6 c > > which is correct, BUT > select distinct val1, MAX(id), val2 from table order by val1 > > it returns > > id val1 val2 > 3 a 1 > 4 b 2 > 6 c 3 <--- incorrect > > it then returns the FIRST "hit" for val2 in the db, and NOT the one in > the row with the max id... > > Can I do this with one query? ( PS, I cannot use MAX on val2 or val3, > they are text) > > > Thanks -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
