Aren't you grouping on IDt? something like ? : select t2.IDt,t2.ID,t2.Num,max(t2.version) from table1 as t1, tabl2 as t2 where t1.num=t2.num and t1.state!='new' group by t2.IDt
Cheers, Geert-Jan 2010/7/26 Michael Stroh <st...@astroh.org> > Hi everyone and thanks in advance for the help. I have a query that I'd > like to perform using two tables but am not sure what the best way to > perform it short of creating a loop in my code and performing multiple > queries. > > I have two tables. The first table acts as a master table of sorts and Num > in Table1 maps directly to Num in Table2. One way to think of this is that > I'm performing a query on Table2 and grouping the records by MAX(version) > but I am not interested in records if state = new in Table1 for the value of > Num in Table2. I've tried to give an example below. > > Table1: > Num, state > 1 final > 2 new > 3 final > > Table2: > Num, ID, IDt, version > 1 1 100 1 > 1 2 101 1 > 1 3 102 1 > 2 4 100 2 > 2 5 103 1 > 3 6 100 2 > 3 7 103 1 > 3 8 104 1 > > Preferred result: > IDt, ID, Num, MAX(version) > 100 6 3 2 > 101 2 1 1 > 102 3 1 1 > 103 7 3 1 > 104 8 3 1 > > Cheers, > Michael > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=gbr...@gmail.com > >
