FirstSortedValue VS Max

[#1]

Hi,

i don't understand because the 2 scripts are different....

scriptA:

load key,

max(x) as x

resident myTable

group by key;

left join

LOAD key,

x,

val

resident myTable;

scriptB:

load key,

firstsortedvalue(val, -x) as val

resident myTable

group by key;

Thanks!

[Oleg_Troyansky]

From the first look the thwo scripts should produce identical results - except maybe for a minor syntax issue. If I'm not mistaken, you need to explicitly multiply x * -1 instead of simply using "-x".

Other than that, I can't see a difference...

[johnw · Accepted solution]

Well, there's the trivial fact that the first table has x in it and the second does not. I assume that's not your concern.

They can produce different results depending on your data. Take this table for example:

myTable:
key, x, val
1, 1, a
1, 2, b
1, 2, c
2, 1, a
2, 2, b

I expect these tables to result:

scriptA:
key, x, val
1, 2, b
1, 2, c
2, 2, b

scriptB:
key, val
1, null
2, b

The difference results from having two different val corresponding to key = 1 and x = 2. Firstsortedvalue() will return null when there are multiple values that are first by your sort order, while your left join will generate an extra row for each such value.

I don't know if that's the specific difference you're seeing since you gave no example data or example results, but it's A difference between the two scripts.

[#4]

Thank you all for the answers!

My problem is how John says: Firstsortedvalue() return null when there are multiple values that are first by your sort order.

Thansk again