Sql-server – How to re-index a column using SQL Server Compact Edition

hierarchysql serversql-server-compact

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?

Best Answer

SQL Server Compact does not support CTE or temp tables. Why not simply implement the solution in code, since the SQL Compact database runs in process anyway?

Related Question