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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment