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

3 votes

2 answers

841 views

                          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?
                        

Asked by Code Maverick (131 rep)

Oct 13, 2015, 06:10 PM
Last activity: Oct 14, 2015, 02:14 PM

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

Related Questions