Group by question

[chematos]

Hi everyone,

This is the thing:

Imaging that I have this fields:

Group

Category

Date

Amount

Now I want to calculate the max(Amount) and get the date of that maximum grouping by Group and Category.

And this is the problem:

If I do the group by with Group and Category, I don´t know in which date I had the maximum, the solution I thought is doing a link with the amount or with a key composed by  Group&Category&Amount but I don´t think it would be the best solution, overall if there are repetead maximums.

And I can´t group by Group, Category and Date, because I want the maximum amount of those Dates and not the maximum per day.

The date must be only an added information field.

If is not clear enough, ask me please.

I´m doing all in the load script, FYI.

Any ideas??

Thank´s

[swuehl · Accepted solution]

You have only one Amount record per Date, Category and Group or do you need to sum amounts prior getting the max?

I think you could try using max(Amount) and FirstSortedValue:

LOAD

Group,

Category,

max(Amount) as MaxAmount,

FirstSortedValue(Distinct Date, -Amount) as MaxAmountDate

resident YourTable group by Group, Category;

[chematos]

As successful as ever!

Thank you, it´s a great function this FirstSortedValue.

Regards,

Chema