I'm trying to create an aggregate table using the combination of two others.
Feed Table A:
PrimaryKey, Score, DateField
1, 50, 1/1/2016
2, 12, 1/1/2016
1, 75, 1/12/2016
1, 80, 1/15/2016
2, 35, 1/16/2016
Expected Result (Table B):
PrimaryKey, Score, LastScore, OtherFields...
1, 80, 75, data...
2, 35, 12, data...
In record 1, 80 is the most recent record, 75 is the 2nd most recent record. For all records in Table B, this is the rule.
The condition is that there's over 25Million Records in Table A, and 250Million Records in Table B. Table B gets routinely updated by Table A.
I'm using statements like this:
max(case when seqnum = 1 then Scores end) as score,
max(case when seqnum = 2 then Scores end) as score_previous
from (select a.*,
row_number() over (partition by RTPropertyUniqueIdentifier order by LoadDate desc) as seqnum
from PROPERTY_SCORE a
where seqnum <= 2 -- This should help performance of the `group by`
group by a.RTPropertyUniqueIdentifier
My problem is that the response of this query response changes every time it runs. I also tried to save the query as a view, but that resulted in the same random responses.