Top X from joined table

[bnhcomputing]

Table 1:

SalePerson_ID, SalesPersonName.


Table 2:

SalesPerson_ID, SaleDate


The two tables are joined by SalesPerson_ID.

SELECT * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON
T1.SalesPerson_ID = T2.SalesPerson_ID

Gives back all sales, I only want the last five (5) for EACH sales
person.


There needs to be some sort of "SELECT TOP 5" and "ORDER BY SalesDate
DESC" but I be damned if I can see it.

SELECT TOP 5 * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON
T1.SalesPerson_ID = T2.SalesPerson_ID
ORDER BY T2.SaleDate DESC

Gives me the last five sales, not the last five for EACH sales person.


Suggestions?

 

[Michel Walsh]

There are at least two solutions: ranking by group or sub-query.

With a subquery, it may look like:

SELECT salesPersonID, saleDate
FROM yourQuery AS a
WHERE saleDate IN( SELECT TOP 5 b.saleDate
FROM yourQuery AS b
WHERE a.salesPersonID = b.salesPersonID
ORDER BY b.saleDate DESC);
ORDER BY salesPersonID, saleDate DESC



where the last

ORDER BY salesPersonID, saleDate DESC


is optional (only supplied to easily 'see' that the solution is ok).


By ranking, it may be faster. Assuming you have no duplicated couple
(salesPersonID, saleDate):



SELECT a.salesPersonID, a.saleDate
FROM yourQuery AS a INNER JOIN yourQuery AS b
ON a.salesPersonID = b.salesPersonID
AND a.salesDate <= b.salesDate
GROUP BY a.salesPersonID, a.saleDate
HAVING COUNT(*) <=5



Note that it may be that you only need table2, if so, use table2, instead of
your query (it could be faster, again) to find the top 5 and only join with
table1 AFTER you found the top 5, by salesPersonID.


Vanderghast, Access MVP