Re: Mysql query and PHP loops

did you also play with group by? what does this return? select * from yourTable group by val1 ciao SVEN 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