I have a table of Ids which are versioned by timestamp (its just a DataVault satellite table) and I´d like to utilize window functions to get the most recent version of each Id like so:
SELECT a.* FROM odp.s_entity_access a inner join ( SELECT last_value(_id) OVER(PARTITION BY entity_id order by lddts) as rid FROM odp.s_entity_access ) b on a._id = b.rid ;
LAST_VALUE() seems to always return the value in the current_row instead of the last row in its partition, so my query just returns all rows.
If I don’t use PARTITION BY I get the last Rowid of all rows
lddts are the Primary key, I also joined them together and hashed them as an artifical primary key but this returns the same results as using
As A workaround I might using GROUP BY and join via the timestamps (as _id won’t be sortable/linear ascending I guess), but I am afraid that this might be significantly slower with a lot of rows.
Am I using LAST_VALUE() in a wrong way, or is this a bug?
EDIT: I found a good workaround utilizing row_number() instead of last_value() but still its not clear to me why one window function works as expected in the same partition/order by and the other does not
SELECT a.* FROM odp.s_entity_access a inner join ( SELECT ROW_NUMBER() OVER(PARTITION BY entity_id order by lddts desc) as ind,_id FROM odp.s_entity_access ) b on b.ind = 1 and a._id = b._id ;