I think this explains things...
Number of items sold in a transaction
Department / Customer / SalesPersonID / Sales
A / Mr Smith / 1 / 3
A / Mr Brown / 2 / 2
A / Mr Roberts / 1 / 5
A / Mrs Smith / 2 / 1
A / Mr Andrews / 2 / 1
A / Mrs Black / 3 / 2
B / Mr Smith / 1 / 1
(there will be many departments)
SELECT Department, SalesPersonID, totSales FROM $table GROUP BY Department, SalesPersonID ORDER BY SUM(Sales) AS totSales
This will group and return them all, but I only want the TOP 2 per department.
Thanks in advance.
Should have explained it in full in your original post
This article covers what you are looking for - http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
You should read it all, but your answer lies under "Select the top N rows from each group"