How to use group by with more than one condition

[#1]

Hello,

I'm wondering if anyone can tell me how I can use group by if I have more than one condition.

For example

You have a file contaning

ArticleNumber, Price, Date

1,20,20110101

1,15,20110630

I want to get the price with the highest date.

If I use

Load

     ArticleNumber,

     Price,

     Max(Date) as Date

From xxx

group by ArticleNumber, Price;

Then the result looks like this

1,20,20110630

1,15,20110630

I.e. I get the highest date but I have no way of knowing which Price to pick.

If I use

Load

     ArticleNumber,

     Max(Price) as Price,

     Max(Date) as Date

From xxx

group by ArticleNumber;

Then I get

1,20,20110630

Then my price really belongs to another date.

I want the Price that belongs to the highest value in Date.

In this example the correct answer is

1,15,20110630

So is there any way to solve this?

br

Martin

[matt_crowther · Accepted solution]

Martin,

Try looking at the function: FirstSortedValue() - it achieves similar results for me.

eg: firstsortedvalue(Price, Date) will give give the Price on the first sorted data - you can change the sort order within the Grouping to get the highest or lowest date.

You may need a '1 as Group_Field' to your load to allow the Group By to work correctly.

All the best,

Matt - Visual Analytics Ltd

[#3]

I usually solve this requirement using "lastvalue()" combined with "Group By" and "Order By"

In your case:

          Load

               ArticleNumber

              ,lastvalue(Price)                         

              ,lastvalue(Date)                    

          resident xxx

          Group by ArticleNumber

          Order by Date

          ;

Bye.

Marco.

[#4]

Can you try the below script:

Load ArticleNumber,Max(Date) as Date

From xxx group by ArticleNumber;

Join

Load * From xxx;

Kiran.