Problem solve Get help with specific problems with your technologies, process and projects.

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 last published in July 2002

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.