This is a nice puzzle.

As my main DBMS is Teradata I wrote a solution for it using Analytical functions (needs TD14.10+):

SELECT dt.*,   -- find the last item in the stack with the same position   Last_Value(val IGNORE NULLS)   Over (PARTITION BY pos         ORDER BY i) AS top_of_stack_valFROM  (    SELECT st.*,      -- calculate the number of items in the stack      Sum(CASE WHEN op = 'I' THEN 1 ELSE -1 end)       Over (ORDER BY i            ROWS Unbounded Preceding) AS pos   FROM stack_trace AS st ) AS dt;

This solution works for Oracle, too, but PostgreSQL & SQL Server don't support the IGNORE NULLS option for LAST_VALUE and emulating it is quite complicated, e.g see Itzk Ben-Gan's The Last non NULL Puzzle

Edit: In fact it's not that complex, I forgot Itzik's 2nd solution, the old piggyback trick ;-)

Martin Smith's approach will work for all four DBMSes.

Personally I doubt that you will end up finding SQL that you can just use in all of SQL Server, Teradata, Postgres, and Oracle and that has acceptable performance if the table is at all large.

A SQL Server solution (demo) would be as follows

SELECT i,       SUBSTRING(MAX(FORMAT(i, 'D10') + val) OVER (PARTITION BY Pos ORDER BY i                                                      ROWS UNBOUNDED PRECEDING), 11, 8000)FROM   (SELECT st.*,               sum(CASE WHEN op = 'I' THEN 1 ELSE -1 END)                   OVER (ORDER BY i ROWS UNBOUNDED PRECEDING) AS pos        FROM   stack_trace st) t1ORDER  BY i; 

Although it does require an additional step -

Generic solution for Hive, Teradata, Oracle, SQL Server and PostgreSQL

select      s.i           ,min (s.val) over            (                partition by    s.depth                               ,s.depth_val_seq            )                                   as top_of_stack_val            from       (select      s.i                       ,s.val                       ,s.depth                                               ,count (s.val) over                         (                            partition by    s.depth                            order by        s.i                            rows            between unbounded preceding and current row                        )                                                                   as depth_val_seq                                     from       (select      s.i                                   ,s.val                                                                       ,sum (case s.op when 'I' then 1 else -1 end)   over                                    (                                        order by        s.i                                        rows            between unbounded preceding and current row                                    )                                                                   as depth                                     from        stack_trace s                        )                        s            )            s order by    i;