GROUP BY but limit to top 2 per group

[johnsmith153]

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

 

[fenway]

The "simplest" method is to keep a user-defined variable, which you reset per "group", and simply filter out the rows once you've hit your "limit" -- 2.

 

Of course, by "simplest " I mean easiest to type, not most efficient.  If you want the latter, you'll need to forgo examining each record, and use an derived table to get the N-most per group (and dealing with the edge case of there not being N rows), and then join that up the chain.