Thursday, January 14, 2010

Select Top N Number of Records for an ID or Grouping

How do I select the top 'N' records from a table by some sort of grouping?

This example selects the top 5 invoice records for each customer by creating a grouping on the Customer table's primary key: CustID

SELECT *
FROM dbo.Invoice AS i
WHERE InvoiceID IN
(
SELECT TOP 5 InvoiceID
FROM dbo.Invoice AS i2
WHERE i.CustID = i2.CustID
ORDER BY i.PostDate DESC
)
ORDER BY i.CustID

You can also change this to any type of grouping: Category of customer, amount billed, highest totals by region... whatever you want.