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

    Login 

 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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top