clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805502 fiddles created (40742 in the last week).

CREATE TABLE MyTable (Pos INT UNIQUE, X INT) INSERT INTO MyTable VALUES (3, 2) INSERT INTO MyTable VALUES (5, 0) INSERT INTO MyTable VALUES (6, 0) INSERT INTO MyTable VALUES (9, 0) INSERT INTO MyTable VALUES (43, 9) INSERT INTO MyTable VALUES (53, 8) INSERT INTO MyTable VALUES (56, 0) INSERT INTO MyTable VALUES (81, 0) INSERT INTO MyTable VALUES (163, 1) INSERT INTO MyTable VALUES (9716, 0)
10 rows affected
 hidden batch(es)


WITH StartPoints AS ( SELECT *, GroupId = COUNT(NULLIF(X, 0)) OVER (ORDER BY Pos) FROM MyTable ) SELECT Pos, X, Y = MIN(NULLIF(X, 0)) OVER (PARTITION BY GroupId) FROM StartPoints ORDER BY Pos;
Pos X Y
3 2 2
5 0 2
6 0 2
9 0 2
43 9 9
53 8 8
56 0 8
81 0 8
163 1 1
9716 0 1
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)