Q

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 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

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