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 =3D 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=3Dgcdmg-mysql-2@m.gmane.o rg

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-colum n-group-row.htm=
l


-----Original Message-----
From: Michael Stroh [mailto:stroh@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 lik=
e 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) b=
ut I am not interested in records if state =3D 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=3Dgtowey@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 th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Yet another query question

--000e0cd6a90677b311048c51a811
Content-Type: text/plain; charset=ISO-8859-1

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

> 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=gbrits@gmail.com
>
>

--000e0cd6a90677b311048c51a811--

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?
>=20
> something like ? :
> select t2.IDt,t2.ID,t2.Num,max(t2.version) from table1 as t1, tabl2 as =
t2
> where t1.num=3Dt2.num and t1.state!=3D'new' group by t2.IDt
>=20
> Cheers,
> Geert-Jan
>=20
> 2010/7/26 Michael Stroh
>=20
>> 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.
>>=20
>> 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 =3D new in Table1 for the =
value of
>> Num in Table2. I've tried to give an example below.
>>=20
>> Table1:
>> Num, state
>> 1 final
>> 2 new
>> 3 final
>>=20
>> 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
>>=20
>> 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
>>=20
>> Cheers,
>> Michael
>>=20
>>=20
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dgbrits@gmail.com
>>=20
>>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg