Top 3 items in every product group

[RealmOfConfusion]

Say I have a table that contains a list of product groups (Alpha, Bravo, Charlie), individual products within each group (A01, A02 etc. B01, B02 etc.) and the price of each product.



What I need is to output the 3 highest value products within every product group, so the output would look something like this:

GROUP	PRODUCT	PRICE
Alpha	A03	2617
Alpha	A07	2309
Alpha	A02	1926
Bravo	B04	983
Bravo	B05	983
Bravo	B02	872
Charlie	C02	3615
Charlie	C07	1762
Charlie	C01	760

[CJ_London]

try something like this

SELECT *
FROM myTable
WHERE Product in (SELECT TOP 3 Product FROM myTable AS T WHERE Group=myTable.Group ORDER BY Price Desc)

note Group is a reserved word, so using it will have unintended consequences, recommend you change it to ProdGroup or similar

[RealmOfConfusion]

Hi Ajax, thanks for the suggestion. Data table is called myTable, and I've changed by field names (prod_group, prod_name, prod_price), but using the following SQL amended from your suggestion, I'm getting a syntax error:

SELECT *
FROM myTable
WHERE prod_name IN (SELECT TOP 3 prod_name FROM myTable AS T WHERE Group=myTable.Group ORDER BY prod_price Desc)

It's probably me doing something incredibly obviously stupid, but any suggestions as to how to fix would be appreciated!

[CJ_London]

Probably as previously advised

note Group is a reserved word, so using it will have unintended consequences, recommend you change it to ProdGroup or similar

[RealmOfConfusion]

Yup, as expected, it was me being incredibly stupid. I'd assumed that your use of "group" was the group function and not my badly named field name.

Changed both instances of group in your syntax to prod_group and that gives me exactly what I needed.

Many thanks for your assistance.

[CJ_London]

glad to help!