TOP values

[Dina]

Hi,

I am having trouble returning the top values in a GROUP BY or TOTALS query.
I would like to GROUP BY an attribute (Accounts) and return the top 5 values
(Deposits) per account holder.

I am having trouble because the GROUB BY MAX function only returns one value
(the max value) PER ACCOUNT and I need it to return the highest 5 values.

Your help is appreciated. Thanks in advance.
Dina

 

[Allen Browne]

See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

 

[Dina]

Hi Allen,

Many thanks for the link. In the link you provided me, OrderID is unique.
Do I HAVE to have a unique attribute for this to work??

Thank you,
Dina

 

[Allen Browne]

It doesn't have to be a single field, but there does have to be some way to
determine which fields constitute the related records if you are to choose
the top 5 for each one.

 

[Dina]

Ok, thanks Allen. I managed to use your advice & it worked.

As a follow up/related question:

Instead of selecting the Top 5 records per account holder in the subquery,
is there a way to select every 2nd (or 3rd etc) record per account holder?

Thanks again.