How do I get the top ten within a subquery? For example,
Table Customer cust_code cust_name
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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in July 2002