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:

        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?


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?

  • That's actually what's currently being done. I just wanted to see if there was a way to accomplish this on the SQL side. It sounds like it would be way more trouble than it's worth. Thanks. – Code Maverick Oct 14 '15 at 13:12

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
    INTO #Parents
    FROM TemplateItem 

    --looping variables
          @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
        --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
        SELECT Id
        FROM TemplateItem
        WHERE ParentID = @curParentId
        ORDER BY ListIndex

        INSERT #Hierarchy
            (ParentID, ListIndex)
        SELECT  t.Id,
                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
  • Sorry, forgot the -1 to reset the NewIndex to 0. Should be identical now to what you had. – Steve Mangiameli Oct 13 '15 at 20:47
  • So does your answer assume that this is to be consumed inside of a cursor or the like to get the hierarchical requirement to work? – Code Maverick Oct 13 '15 at 21:24
  • Otherwise, hierarchical data will not work. I need each relationship to have its own set of 0 to N indexes. – Code Maverick Oct 13 '15 at 21:30
  • Gotcha, missed the hiarchial model. Yeah, you'd need to grab the parent IDs first and then loop through them for each of the children. Windowing functions would definitely make this much easier. I can get that scripted later tonight or tomorrow if you need an example. Sounds like you have the idea though. – Steve Mangiameli Oct 13 '15 at 21:57
  • I think this is what you are looking for. Quite a bit more code, but it should approximate what you would need to do. I have to admit, this is untested since I don't have a dataset to test with. – Steve Mangiameli Oct 14 '15 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.