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?