Re: Mysql query and PHP loops

On further reflection, my first attempt works for the specific example but may not in the general case. Try using a combination of max( ...id ) and min( ..val2 ) and add t1.val2 to the group by clause. This might work, but I've deleted my test files now! On Friday 01 Aug 2003 7:04 am, Nicholas Robinson wrote: > 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