Common Table Expression in sqlite using rowid

I found a good article on converting adjacency to nested sets at http://ift.tt/1x7t9gk

The SQL language used is Microsoft SQL Server (I think) and I am trying to convert the examples given in the article to sqlite (as this is what I have easy access to on my Macbook).

The problem I appear to be having is converting the part of the overall CTE query to do with the Employee Rows

EmployeeRows AS
(
    SELECT
         EmployeeLevels.*,
         ROW_NUMBER() OVER (ORDER BY thePath) AS Row
    FROM EmployeeLevels
)

I converted this to

EmployeeRows AS
(
    SELECT
         EmployeeLevels.*,
         rowid AS Row
    FROM EmployeeLevels
    ORDER BY thePath
)

and the CTE query runs (no syntax errors) but the output I get is a table without the Row and Lft and Rgt columns populated

ProductName  ProductID   ParentProductID  TreePath    HLevel      Row         Lft         Rgt       
-----------  ----------  ---------------  ----------  ----------  ----------  ----------  ----------
Baby Goods   0                            0           1                                             
Baby Food    10          0                0.10        2                                             
All Ages Ba  100         10               0.10.100    3                                             
Strawberry   200         100              0.10.100.2  4                                             
Baby Cereal  250         100              0.10.100.2  4                                             
Beginners    150         10               0.10.150    3                                             
Formula Mil  300         150              0.10.150.3  4                                             
Heinz Formu  310         300              0.10.150.3  5                                             
Nappies      20          0                0.20        2                                             
Small Pack   400         20               0.20.400    3                                             
Bulk Pack N  450         20               0.20.450    3                                             

I think the start of the problem is the Row is not getting populated and therefore the Lft and Rgt columns do not get populated by the following parts of the query.

Are there any sqlite experts out there to tell me:

• am I translating the rowid part of the query correctly


• does sqlite support a rowid in a part of a CTE query


• is there a better way? :)

Any help appreciated :)