Syntax for SELECT TOP (I think) Query

[Foyer]

Hi all,

I'm trying to build a report that shows every product category and then lists only a few products (not all products) within each category. I want to limit the products to no more than 3-5 for each category. I keep thinking I need to use a SELECT TOP query but the more I research it the more it seems it should only work with calculated fields so maybe I'm going about it the wrong way? I can't seem to figure out the syntax for the query to build the report.



The two fields I am using are ProductName and ProductCategory from the ProductsT table, so the report would be grouped by ProductCategory with a selection of 3-5 ProductName below it.

Thanks for your advice.

[June7]

Why would you think it only works with calculated fields?

Review http://allenbrowne.com/subquery-01.html#TopN

[Foyer]

Just all of the tutorials I could find always included a calculated field so I thought maybe that's what I'm missing.

I built two test tables with the fields from a couple of the examples in your link and some sample data and then copied the SQL in the hopes I could better understand how they worked but neither query returned the proper results.

One was the meter table for the "Get the value in another record" example and the other was the Orders Table for the "Top n Records per group" example. I'm sorry, I'm not a database designer and this coding stuff is super confusing to me.

Tanya

[June7]

It's a proven technique and a frequently referenced tutorial.

Would have to see what you did to advise further. Or your actual data. Can attach file to post. Follow instructions at bottom of my post.

[Foyer]

Here it is: Widgets.zip

[June7]

Does this give what you expect:

SELECT * FROM ProductT WHERE ProductID IN (SELECT TOP 3 ProductID FROM ProductT AS Dupe WHERE Dupe.Category=ProductT.Category ORDER BY ProductID);

[Foyer]

Yes! Thank you!