Top Values by group

[Jack Darsa]

Hello ,
I need to calculate top values separated by groups.
The input table is like:

A B C
1 1 10
1 2 12
1 1 9
2 2 20
2 1 15
2 2 10


The group fields are A and B ,C contains the value to retrieve.
The result should be similar to:
A B C
1 1 10
1 2 12
2 1 15
2 2 20

The real table contains over half a million records and thousands of groups.
The number of groups varies with the content of data. A stop query will
solve the problem ,but:
- Only for top single value for each groups
- A lot of time 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

 

[Jack Darsa]

Where is written " a stop query..." should be replaced by "Two consecutive
queries (second based on the first one)".
Sorry for the mistake and thank you for your help.

 

[Michel Walsh]

Hi,


For a TOP 1, the fastest is probably:


SELECT f1, f2, MAX(f3)
FROM myTable
GROUP BY f1, f2



For relatively small to medium table, a GROUP TOP N can be simulated by,
[ n ] being a parameter:


SELECT a.f1, a.f2, a.f3
FROM myTable As a LEFT JOIN myTable As b
ON (a.f1=b.f1) AND (a.f2=b.f2) AND (a.f3>b.f3)
GROUP BY a.f1, a.f2, a.f3
HAVING COUNT(*) < [ n ]

ORDER BY a.f1, a.f2



Note that the ORDER BY is facultative (not really part of the
solution, but nice for presentation).


You can also try a sub-select query, if you have a primary key made of a
single field:

SELECT a.*
FROM myTable As a
WHERE a.pk IN ( SELECT TOP n b.pk
FROM myTable As b
WHERE a.f1=b.f1 AND a.f2=b.f2
ORDER BY b.f3 DESC )

ORDER BY a.f1, a.f2


In that case, the query is updateable, but n cannot be a parameter.




Hoping it may help,
Vanderghast, Access MVP