Top 5 records per group

[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

 

[Michel Walsh]

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

 

[Van T. Dinh]

See MSKB 210039

http://support.microsoft.com/kb/210039

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jay]

Many thanks everyone for all the help & suggestions.

Rgds,

Jason