For Next Loop

[Sean]

I have the following layout.

Market Sic ProdClass YTD

For each change in Market (I have + 200 in a database of
600,000+ rows of data monthly)I would like to be able to
return the top N values for ProdClass.

I have tried the sort descending and topN value but it
only returns the topN values and not a grouping by each
Market.

Any ideas?

Thanks.

 

[Gerald Stanley]

If your layout had an extra column that uniquely identified
the row (e.g. rowId) the SQL would be along the lines of

SELECT DISTINCT Market, Sic, ProdClass, YTD
FROM YourTable T1
WHERE rowId IN
(SELECT TOP N rowId FROM YourTable T2 WHERE T2.Market =
T1.Market AND T2.prodClass = T1.prodClass ORDER BY YTD DESC)

The above is pure untested aircode.
Hope This Helps
Gerald Stanley MCSD