Firstsortedvalue weird behavior

[SGV]

Hello, please see scenario below: 

App A: 

Based on sample data:

Load * inline [
Date, Price
1/10/2025,10
1/10/2025,12
1/10/2025,15
2/10/2025,18
2/10/2025,19
3/10/2025,25
];

Close = Avg(price)

App B:

Without Distinct keyword

Close = Avg(price)

With Distinct Keyword

Close = Avg(price)

Not sure what is causing the Firstsortedvalue to work with a Distinct. Since the Aggregation should output only a distinct value per row by default right? Maybe beacuse the Date field is derived from a Timestamp field?

My question is, am I hallucinating? or have I missed a simple trick. Please assist.

Thanks!

@rwunderlich @hic 

[rwunderlich · Accepted solution]

With some input from my colleague @Oleg_Troyansky I think I can explain. First you need to add a second 3/10 value to make your example equal to App B. I'll add a record id for illustration. 

Load *, RecNo() as RecNo inline [
Date, Price
1/10/2025,15
2/10/2025,18
2/10/2025,19
3/10/2025,25
3/10/2025,30
];

While it's true that the Aggr() returns three values, they get associated back to the five Date rows. You can see this if you add the RecNo to a chart:

Hence there are two values associated with the last Date, so you must use DISTINCT.

-Rob