How to add a new column in a View in sqlite?

Question

I have this database in sqlite (table1):

+-----+-------+-------+
| _id | name  | level |
+-----+-------+-------+
| 1   | Mike  | 3     |
| 2   | John  | 2     |
| 3   | Bob   | 2     |
| 4   | David | 1     |
| 5   | Tom   | 2     |
+-----+-------+-------+

I want to create a view with all elements of level 2 and then to add a new column indicating the order of the row in the new table. That is, I would want this result:

+-------+------+
| index | name |
+-------+------+
| 1     | John |
| 2     | Bob  |
| 3     | Tom  |
+-------+------+

I have tried:

CREATE VIEW words AS SELECT _id as index, name FROM table1;

But then I get:

+-------+------+
| index | name |
+-------+------+
| 2     | John |
| 3     | Bob  |
| 5     | Tom  |
+-------+------+

I suppose it should be something as:

CREATE VIEW words AS SELECT XXXX as index, name FROM table 1;

What should I use instead of XXXX?

Answer 1

When ordered by _id, the number of rows up to and including this one is the same as the number of rows where the _id value is less than or equal to this row's _id:

CREATE VIEW words AS
  SELECT (SELECT COUNT(*)
          FROM table1 b
          WHERE level = 2
            AND b._id <= a._id) AS "index",
         name
  FROM table1 a
  WHERE level = 2;

(The computation itself does not actually require ORDER BY _id because the order of the rows does not matter when we're just counting them.)

Please note that words is not guaranteed to be sorted; add ORDER BY "index" if needed.

---

And this is, of course, not very efficient.