selecting top 100 values in a query

[roddy17]

ok, i am trying to build a query that i can use for a report.
What i am trying to do is to run a query which would give me the top 100 ITEMS in each CATEGORY in the MAIN_TABLE.
(the ITEMS have an extra field which gives them a numerical value to be ranked on) (the CATEGORIES do not have a hierarchy, they are all equal).
And if that could be coded, where in the query would it show up?
Previously i tried using the 'Top Values' field in the properties box of my query but it only gave me the top 100 ITEMS no matter what CATEGORY they belong to instead of the top 100 of each.
thankyou

 

[jjonesal]

Try using this in the SQL view of the query:


SELECT DISTINCT x.category, x.otherfield, x.id
FROM [MAIN_TABLE] AS x
WHERE (((x.id) In (SELECT TOP 100 y.id
FROM [MAIN_TABLE] y
WHERE x.category = y.category
ORDER BY y.id)));


where
category = the field name containing your category
otherfield is any other field (or you can add more) that you want to use in your report
id = the unique field that you use for ranking


Hope this helps...
J. Jones
jjones@cybrtyme.com

 

[Aivars]

Hi, Roddy!

Look at thread705-102904 on what I posted function what would solved your problem.

Aivars

 

[roddy17]

hi there,
i have been offline for a couple of days.
I thankyou for your responses. Hopefully i will have a chance to try them over the next day or so.
thanks very much.