I have a table MagIssues in an Access database of magazine issues. I need to select the most recent dated issue that is published. I have tried this but it is not working:
SELECT * WHERE (((MagIssues.MagDate) IN (select Max(MagDate) FROM MagIssues)) AND ((MagIssues.MagPublish)=1))
Can you see what is wrong?
Yes, although the parenthese are obscuring it a little. You probably have unpublished issues that are more recent than the most recent published one. To see why, let's rewrite the query, paying special attention to the parentheses:
select * from MagIssues where ( ( (MagIssues.MagDate) in ( select Max(MagDate) from MagIssues ) ) and ( (MagIssues.MagPublish)=1 ) )
Those extra parentheses, inserted on your behalf by your friendly Design View wizard, are not really necessary, so let's rewrite it again without them:
select * from MagIssues where MagIssues.MagDate in ( select Max(MagDate) from MagIssues ) and MagIssues.MagPublish=1
The subquery gets the most recent date of all issues, both published and unpublished. So if the most recent issue isn't published, the AND condition will fail, and you get no results.
What you want is to move that condition down into the subquery, to get the date of the most recent published issue:
select * from MagIssues where MagIssues.MagDate in ( select Max(MagDate) from MagIssues where MagIssues.MagPublish=1 )
By the way, this is a good example of a scalar subselect. The outer query does not really require the IN keyword (although it works fine). The result of a scalar subselect is a single value, and can be tested for with an equal sign instead. I'm sorry, I don't know which is faster in execution, but if either method is, it'll be the equal sign rather than the IN.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs 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, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in July 2002