How can I re-index a column using SQL Server Compact Edition?

We have a table TemplateItem that has a column ListIndex that stores the order in which we want the items to show in the UI. This table is self-referencing / hierarchical.

The query requirement is that the column ListIndex has to start from 0 and be sequential for each parent / child(ren) relationship.

The query I came up with is as follows:

SELECT  Id,
        ParentId,
        Sub.NewIndex AS ListIndex

FROM    TemplateItem
JOIN    ( 
            SELECT  Id, 
                    ROW_NUMBER() OVER 
                    ( 
                        PARTITION BY ParentId ORDER BY ListIndex 
                    ) 
                    - 1 AS NewIndex 

            FROM    TemplateItem
        ) 
        AS Sub ON TemplateItem.Id = Sub.Id

( unnecessary bits of query removed for readability )

This works perfectly fine in SQL Server 2008 R2.

However, I now need to use this same functionality in SQL Server Compact Edition 4.0 but, as you may already know, it will fail.

It does so because there is no ROW_NUMBER() in SQL Server Compact Edition 4.0.

How can I accomplish this same functionality?

Since you can't generate the ID artificially with a windowing function, you will need to add a step and create it as an IDENTITY field in another table.

Something like this

--temp table to store all the new data
    CREATE TABLE #Hierarchy
    (
          HId INT IDENTITY  
        , ParentID INT
        , ListIndex INT
    )

    --distinct parentids in hierarchial model
    SELECT DISTINCT ParentID
    INTO #Parents
    FROM TemplateItem 

    --looping variables
    DECLARE 
          @loopCnt INT
        , @curParentId INT

    --set looping variables (not a fan of cursors)
    SELECT @loopCnt=COUNT(ParentID), @curParentId=-1
    FROM #Parents

    --loop through parentids and reset the ListIndex
    WHILE @loopCnt > 0
    BEGIN
        --get the next ParentID
        SELECT @curParentId=MIN(ParentID)
        FROM #Parents
        WHERE ParentID > @curParentId

        --temp table to store the refreshed ListIndexes by ID
        CREATE TABLE #NewIndex
        (
              NewIndex INT IDENTITY
            , Id INT
        )

        INSERT #NewIndex
            (Id)
        SELECT Id
        FROM TemplateItem
        WHERE ParentID = @curParentId
        ORDER BY ListIndex

        INSERT #Hierarchy
            (ParentID, ListIndex)
        SELECT  t.Id,
                t.ParentId,
                Sub.NewIndex - 1 AS ListIndex -->Reset to 0 here

        FROM    TemplateItem t
        JOIN    #NewIndex Sub ON Sub.Id = t.Id

        --CleanUp and continue/stop loop
        DROP TABLE #NewIndex
        SET @loopCnt=@loopCnt-1
    END

StackExchange Database Administrators Q#117914, answer score: 2