Loading row with max value

[#1]

Hi

I have fields item, quantity

ie

item, quantity, region

consumables, 5, spain

consumables, 8, brazil

hardware, 7, england

hardware, 9, england

hardware, 10, france

For each item how would I load only the row with the greatest 'quantity' so that my expected output would look like the following :

consumables, 8, brazil

hardware, 10, france

[pokassov]

load

item,

FirstSortedValue(region, -quantity)     as region,

max(quantity)     as quantity

resident ..

group by item

[sunny_talwar]

You can also try this:

Table:

LOAD item,

          quantity,

          region

FROM ...;

Right Join (Table)

LOAD item,

          Max(quantity) as quantity

Resident Table

Group By item;

Based on your provided sample:

Table:

LOAD * Inline [

item, quantity, region

consumables, 5, spain

consumables, 8, brazil

hardware, 7, england

hardware, 9, england

hardware, 10, france

];

Right Join (Table)

LOAD item,

     Max(quantity) as quantity

Resident Table

Group By item;

Output:

[#4]

This is really good. I have another question. What if for instance for one of the items there were two rows with identical quantities. So the table looks like

Table:

LOAD * Inline [

item, quantity, region

consumables, 5, spain

consumables, 8, brazil

consumables, 8, switzerland

hardware, 7, england

hardware, 9, england

hardware, 10, france

];

In this case you still only want to see one of the two rows output for consumables where quantity is 8. Regardless of the region.

[sunny_talwar]

Not sure what you mean? Looking for this output or something else?

[#6]

Hi Sunny ... No effectively just two rows so that for consumables it picks either of the two lines with the greatest quantity. As below :

consumables,8, ( brazil or switzerland)

hardware,10, france

[sunny_talwar]

Try this:

Table:

LOAD * Inline [

item, quantity, region

consumables, 5, spain

consumables, 8, brazil

consumables, 8, switzerland

hardware, 7, england

hardware, 9, england

hardware, 10, france

];

Right Join (Table)

LOAD item,

     Max(quantity) as quantity

Resident Table

Group By item;

Right Join(Table)

LOAD quantity,

  FirstValue(region) as region

Resident Table

Group By quantity;

[#8]

Sunny this is excellent. Im understanding the pattern of how the joins work now.

If I however had many additional fields for consumables where quantity is equal to 8 - would there be a way to as before only output the two given rows of data irrespective of the additional fields.

Table:

LOAD * Inline [

item, quantity, region, type, name, transport

consumables, 5, spain, indirect, Ben, Air

consumables, 8, brazil, delayed, Alan, Ferry

consumables, 8, spain,  direct, Tony, Rail

consumables, 8, switzerland, indirect, Joe, Air

hardware, 7, england, direct, Curtis, Rail

hardware, 9, england, delayed, John, Air

hardware, 10, france, direct, Dave, Ferry

];

So output would be same as before however only FirstValue returned for the additional fields

[sunny_talwar]

Yup you got it right.

Additional rows won't matter. Only the FirstValue (which can be sorted) will be returned.