I have this view:
ORGANIZATION_ID|INVENTORY_ITEM_ID|ITEM_COST|PERIOD_DATE |
---------------|-----------------|---------|-------------------|
1271| 37061| 49.64|2019-02-28 23:59:59|
1271| 37061| 50.15|2019-01-31 23:59:59|
1271| 37061| 58.99|2018-12-31 23:59:59|
1271| 37061| 56.85|2018-11-30 23:59:59|
1271| 37061| 34.78|2018-10-31 23:59:59|
1271| 37061| 44.91|2018-09-30 23:59:59|
1271| 37061| 44.05|2018-08-31 23:59:59|
1271| 37061| 20.84|2018-07-31 23:59:59|
1271| 37061| 19.95|2018-06-30 23:59:59|
1271| 37061| 18.56|2018-05-31 23:59:59|
1271| 37061| 17.42|2018-04-30 23:59:59|
1271| 37061| 13.62|2018-03-31 23:59:59|
1271| 37061| 13.62|2018-02-28 23:59:59|
1271| 37061| 13.62|2018-01-31 23:59:59|
When I do FIRST_VALUE:
SELECT UNIQUE FIRST_VALUE(ITEM_COST) OVER (ORDER BY PERIOD_DATE DESC)
FROM SOMEVIEW
WHERE INVENTORY_ITEM_ID = 37061
AND ORGANIZATION_ID = 1271
I get:
FIRST_VALUE(ITEM_COST)OVER(ORDERBYPERIOD_DATEDESC)|
--------------------------------------------------|
49.64|
As desired.
When I do LAST_VALUE
SELECT UNIQUE LAST_VALUE(ITEM_COST) OVER (ORDER BY PERIOD_DATE DESC)
FROM SOMEVIEW
WHERE INVENTORY_ITEM_ID = 37061
AND ORGANIZATION_ID = 1271
I get:
LAST_VALUE(ITEM_COST)OVER(ORDERBYPERIOD_DATEDESC)|
-------------------------------------------------|
49.64|
56.85|
44.05|
18.56|
34.78|
19.95|
20.84|
13.62|
58.99|
44.91|
50.15|
17.42|
Shouldn't it be 13.62? And also, why does the select return all those values? I checked the documentation and don't get it.
I even tried adding more columns to the order by such as Org_ID, same result.
