MAX COUNT and its value in PIVOT Table

[gsiva1220]

Hi ,

I facing an issue to know the value which has max count, let me explain you by example.

Input:

Month	Cost
Jan	10
Jan	11
Jan	11
Jan	13
Jan	12
Feb	11
Feb	12
Feb	12
Feb	12
Feb	13

Output :

Month	max( aggr( count(Cost),Month,Cost))	Max Cost Value
Jan	2	11
Feb	3	12

I can able to achieve the max count but I am facing an Issue finding the max cost value. Can you please let me know how can I achive this? Thank in advance.

Regards,

Siva

[uacg0009]

You're welcome, hope it won't affect your performance and works fine.

If there is no problem, please help to closed this questions.

And if any problem please tell me.

Thanks.

Aiolos

[Anonymous]

Zhao, could u pls explain - sign in front aggr

[uacg0009]

Sorry, what's the meaning of "sign in front aggr" ?

Do you mean the function "aggr" ?

Aggr is just a group by, it's not too hard. It just group by Month and Cost to calculate the count(cost).

Thanks.

Aiolos

[Anonymous]

minus sign before aggr function..

[uacg0009]

oops...sorry for that,

because of the firstsortedvalue, I use rank = 1, so the sort is from minus to max. But I want to get the max value, so I use the minus sign.

[Anonymous]

tnx  a lot....