RE: Yet another query question
You'll need to use the technique described here: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: Monday, July 26, 2010 2:50 PM To: MySql Subject: Yet another query question 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 11 100 1 12 101 1 13 102 1 24 100 2 25 103 1 36 100 2 37 103 1 38 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=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Yet another query question
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 11 100 1 12 101 1 13 102 1 24 100 2 25 103 1 36 100 2 37 103 1 38 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
Re: Yet another query question
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 11 100 1 12 101 1 13 102 1 24 100 2 25 103 1 36 100 2 37 103 1 38 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
