List:General Discussion« Previous MessageNext Message »
From:Michael Stroh Date:July 26 2010 11:17pm
Subject:Re: Yet another query question
View as plain text  
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 <stroh@stripped>
> 
>> 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=1
>> 
>> 

Thread
Yet another query questionMichael Stroh26 Jul
  • RE: Yet another query questionGavin Towey26 Jul
  • Re: Yet another query questionGeert-Jan Brits27 Jul
    • Re: Yet another query questionMichael Stroh27 Jul