Ask the Expert

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?


    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: