Q

Select most recent with a condition

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


This was first published in July 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close