I've got two tables, with a one-to-many relationship. The second table (the many) has foreign key to first table and a column with the effective date of that row. I want to join the tables but only include the most recent row (max(effective_date)) for each. Something like:
select * from table1 a,table2 b where (a.key=b.key) and (b.key,b.effective_date in (select key,max(effective_date) as effective_date from table2 group by key));
I'm using DB2. Thanks.
Pretty good try, but you cannot have more than one column involved in the IN (subquery) test, because the subquery is supposed to return what should essentially be a single list of values, not a multi-column result set.
A correlated subquery will do what you want --
select * from table1 a , table2 b where a.key=b.key and b.effective_date = (select max(effective_date) from table2 where key = a.key)
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.