Sure,
Take a look at this simple example. I've created a table that
contains and EmpID, CustID, and a Sales amount. If I want to know
what the top 3 sales amounts were for each employee, I'd select the
employee, the customer, and the sales amount. Then, add a computed
column (Rank) which counts the number of people in the table with the
same employee# and where the Sales amount is greater than or equal to
that of the current record. Finally, I would indicate how many
records I want for each employee (in this case 3).
This method works wonderfully with small recordsets, but as your file
grows, it can take a while, since you have to compute the rank for
each record.
SELECT C.EmpID, C.CustID, C.Sales,
(SELECT Count(C1.EmpID) FROM tbl_Customers C1 WHERE C1.EmpID =
C.EmpID AND C1.Sales >= C.Sales) AS Rank
FROM tbl_Customers AS C
WHERE (SELECT Count(C1.EmpID) FROM tbl_Customers C1 WHERE C1.EmpID =
C.EmpID AND C1.Sales >= C.Sales)<=3
ORDER BY C.EmpID, C.Sales DESC;
You can accomplish generally the same thing with the following query,
which I think will be significantly quicker because of the use of the
join and the aggregate function at the table level rather than the row
level. This one takes advantage of the fact that you can create a
join that uses something other than an equal sign to equate two
fields.
SELECT C.EmpID, C.CustID, C.Sales, Count(C1.EmpID) as Rank
FROM tbl_Customers AS C
LEFT JOIN tbl_Customers as C1
ON C.EmpID = C1.EmpID and C1.Sales >= C.Sales
GROUP BY C.EmpID, C.CustID, C.Sales
Having Count(C1.EmpID) <= 3
ORDER BY C.EmpID, C.Sales DESC
If you don't understand why this works, look at the query without the
aggregation. Notice how for each combination of employee, customer,
sales in the customers table, you have an extra row in the result set
for every record in the table which has the same employee but where
the sales value is > than that of the current record.
SELECT C.EmpID, C.CustID, C.Sales, C1.Sales as SalesC1
FROM tbl_Customers AS C
LEFT JOIN tbl_Customers as C1
ON C.EmpID = C1.EmpID and C1.Sales >= C.Sales
ORDER BY C.EmpID, C.Sales DESC, C1.Sales DESC
--
HTH
Dale Fye
Is it possible to have a query return the top 10 items in multiple
categories? For instance, say I have a table with salesman id,
product
category, customer, and price. Can I get the top 10 customers per
customer
or per category? If I select the dropdown in the query design and put
in
10, I just get 10 rows.
Thanks,
Brian
