Grouped top values help

[Jack Darsa]

Hello ,
I posted a question about grouped top values but i did not express myself
clearly.
The purpose is to extract the value of group fields for the maximum of an
other field.

The "maximum record" could just one record or "n" top records.
Basically it is a query with top value property broken down by froups.
For example if i have a sales table with ,date,salesman and amount, i am
interested to retrieve the salesman ,his total sales for every month. I may
want also the top 3 salesman for each month.

The input table is like:
Month Salesman Sales
1 1 10
1 2 12
1 3 9
2 1 20
2 3 15
2 2 10


The group field is month,salesman and sales contain the value to retrieve.
The result should be similar to:
Month Salesman Sales
1 2 12
2 1 20

If i translate to words:
Month=1 salesman=2 has the highest amount of sales = 12.
Month=2 salesman=1 has the highest amount of sales = 20 and so on.

I will probably calculate also top 3 for every month and will add another
category field,let's say department etc.
I will also need those with the lowest sales etc, but those are variations
of the first solution.


The real table contains over half a million records and thousands of
groups.
The number of groups varies with the content of data. One solution is to
build 2 or 3 consecutive queries,but: this
will rerieve only one top single value for each group and it will take
longer to run.
I need to retrieve the data in one single query and with top values=1,2,..
etc. as much as i need.

Thank you for your help
Jack

 

[Michel Walsh]

Hi,


SELECT a.[Month], a.Salesman, a.Sales
FROM myTable As a INNER JOIN myTable As b
ON a.[Month]=b.[Month] AND a.Sales >= b.Sales
GROUP BY a.[Month], a.Salesman, a.Sales
HAVING COUNT(*) <= [ n ]


should do. [ n ] is the number of "top" you want. Change >= for <= in
the join to get the lowest ones.




Hoping it may help,
Vanderghast, Access MVP