Yes, sorry, you are correct. I am actually grouping on that other column. I'll take a look at this and see if it works for me. Thanks!
Michael On Jul 26, 2010, at 6:10 PM, Geert-Jan Brits wrote: > 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 >> >> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
