OK guys, having a brain fade here.
I have a set-up something like this:
WITH data_in (id, seq, val1, cval1, cval2) AS
(SELECT 1, 2, 'X', 'Y', 'Z' FROM dual UNION ALL
SELECT 1, 4, '', '', '' FROM dual UNION ALL
SELECT 1, 6, 'B', '', 'X' FROM dual UNION ALL
SELECT 1, 8, 'Q', 'Z', '' FROM dual UNION ALL
SELECT 2, 1, 'A', 'A', '' FROM dual UNION ALL
SELECT 2, 2, 'B', '', 'A' FROM dual UNION ALL
SELECT 2, 3, 'X', 'C', '' FROM dual UNION ALL
SELECT 2, 4, 'C', '', 'B' FROM dual UNION ALL
SELECT 2, 5, 'D', 'B', '' FROM dual)
SELECT *
FROM data_in
ORDER BY id,seq
ID SEQ VAL1 CVAL1 CVAL2
---------- ---------- ---- ----- -----
1 2 X Y Z
1 4
1 6 B X
1 8 Q Z
2 1 A A
2 2 B A
2 3 X C
2 4 C B
2 5 D B
9 rows selected.
The seq is a unique sequence number within each id (in reality it's a date, but I'm simplifying here).
What I want to do is return the values of Id, Seq and Val1 normally from the table, but cval1 and cval2 should have the latest non-null value (as defined by seq) for that id. So I'd get results like this:
ID SEQ VAL1 CVAL1 CVAL2
---------- ---------- ---- ----- -----
1 2 X Y Z
1 4 Y Z
1 6 B Y X
1 8 Q Z X
2 1 A A
2 2 B A A
2 3 X C A
2 4 C C B
2 5 D B B
9 rows selected.
I can get the right results with the following query, but I'm sure there must be a better way to do it that doesn't involve a self-join:
SELECT d.id, d.seq, d.val1,
max(d2.cval1) keep (dense_rank last order by decode(d2.cval1,null,1,2),d2.seq) cval1,
max(d2.cval2) keep (dense_rank last order by decode(d2.cval2,null,1,2),d2.seq) cval2
FROM data_in d
INNER JOIN data_in d2
ON d2.id = d.id
AND d2.seq <= d.seq
GROUP BY d.id, d.seq, d.val1
ORDER BY d.id,d.seq