Top 5 records per group

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

Hi,

I have a table (tblPrices) which contains the following fields:
Manufacturer, SoldPrice, OrderID

What I want is a query to return the 5 highest SoldPrices for each
Manufacturer. I have tried the suggested sql in Allen Browne's website but
can't get it to work.

Can anyone advise what the query (& subquery) would look like,l with the
above table and field names. I'm confident that if I can get a working
example I'll understand the principle better.

Many thanks......Jason
 
You can rank by group or, for a relatively small table, use a subquery like:

SELECT a.manufacturer, a.soldPrice, a.orderID
FROM tblPrices AS a
WHERE a.soldPrices = (SELECT TOP 5 b.soldPrices
FROM tblPrices AS b
WHERE b.manufacturer = a.manufacturer
ORDER BY soldPrices DESC)


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top