TSQL Make partitions "gaps and island"

I need to create partitions. Suppose I have this table:

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)

The query result should be this table with a column Y added, Y should be

IF X=0 : the previous value X<>0 (OR NULL, if not exists), ordered by Pos

IF X<>0 : X

Desired answer table looks like this

SELECT * 
FROM MyQuery as a function of MyTable 
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

1 answer

  • answered 2021-10-24 11:57 Charlieface

    This is a type of gaps-and-islands problem.

    There are many solutions, here is one:

    • Use a running conditional count to number the rows that we want to group together
    • Use a partitioned conditional MIN to take the only value that we actually want, per group
    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;
    

    db<>fiddle

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum