Friday, March 9, 2012

Join using max value

I want to join two tables together but only join the where the column
in table 2 (of type date) is the maximum value. Is this possible?

Note table 1 is the main table it is also joined to other tables too.Hello,

Would something like this work?

select
*
from
table1 t1
join table2 t2 on t1.joinkey=t2.joinkey
where
t2.date = (select max(datefield) as max_date from table2)

Greg.

ree32 wrote:
> I want to join two tables together but only join the where the column
> in table 2 (of type date) is the maximum value. Is this possible?
> Note table 1 is the main table it is also joined to other tables too.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. It is a bitch to debug code that you cannot see.|||Actually, if you read his request, he is only asking if its possible.

So a simple YES, (sans any explanation of technique), would appear to
suffice.

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112319257.363540.54270@.f14g2000cwb.googlegro ups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications. It is a bitch to debug code that you cannot see.|||Thanks for the idea, I will test it out later.

"Greg" <gregdenison@.gmail.com> wrote in message news:<1112314603.380779.78130@.g14g2000cwa.googlegroups.c om>...
> Hello,
> Would something like this work?
> select
> *
> from
> table1 t1
> join table2 t2 on t1.joinkey=t2.joinkey
> where
> t2.date = (select max(datefield) as max_date from table2)

No comments:

Post a Comment