Ask the Expert

Another variation of the Top Ten query

How do I get the top ten within a subquery? For example,

Table Customer
 cust_code  
 cust_name 

Table

    Requires Free Membership to View

Activity cust_code act_date act_text

I need a listing for an Access report which gives me ALL customers AND IF there was any activity then limit it to the last two dates.


That's quite an interesting variation. The first part of the requirement suggests an OUTER JOIN, and the second part requires TOP logic.

I tried the following query in Access 97 --

select XX.cust_code
     , XX.cust_name
     , act_date
     , act_text
  from Customer XX 
  left join Activity 
    on XX.cust_code = Activity.cust_code
 where act_date in
       ( select top 2 act_date 
           from Activity
          where cust_code = XX.cust_code
       order by act_date desc )

For some reason, customers which had no activity at all were not included. The way I see it, they should have been. For these customers, the correlated subquery would not return any results, so I'm guessing that the WHERE clause was evaluated false, which means that in this case, it did not evaluate as

 where act_date in
       ( null )

When act_date is null, the customer should be chosen in the LEFT JOIN. I guess Access doesn't see the empty set quite the same as a NULL, and so perhaps this is just another example of always being very careful around NULLs.

Still, we can get the desired results quite easily by brute force --

select XX.cust_code
     , XX.cust_name
     , act_date
     , act_text
  from Customer XX 
 inner join Activity 
    on XX.cust_code = Activity.cust_code
 where act_date in
       ( select top 2 act_date 
           from Activity
          where cust_code = XX.cust_code
       order by act_date desc )
union all
select XX.cust_code
     , XX.cust_name
     , Activity.act_date
     , Activity.act_text
  from Customer XX 
  left join Activity 
    on XX.cust_code = Activity.cust_code
 where act_date is null
order by 
       cust_code
     , act_date desc

Here we are using INNER JOIN syntax in the first half of the UNION ALL, to get all customers with activities, and LEFT JOIN in the other half, explicitly testing for NULL to ensure we get all customers without activity.

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: