MySQL GROUP BY but limit to top 2 per group

Databases
1

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
etc.
(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.

2

Should have explained it in full in your original post :slight_smile:

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”