SQLite SELECT, which returns identifiers and positions

Question

Using the SQLite SELECT statement, I would like to get the position of each result.

Is it possible?

Table:

fk_id   idx
0       0
0       1
0       2
1       0
1       1
1       3
1       4
2       0

The presence of unique [fk_id, idx].

Query:

SELECT `idx`, <??> from `mytable` WHERE `fk_id`=1

Results:

idx   <??>
0     0
1     1
3     2
4     3

<??> is the information "order" / "position" / "index" that I am looking for.

Answer 1

SQLite has no analytic support - closest you can use a subquery:

SELECT mt.idx,
       (SELECT COUNT(*) - 1
          FROM mytable t
         WHERE t.fk_id = mt.fk_id
           AND t.idx <= mt.idx) AS position
  FROM mytable mt
 WHERE mt.fk_id = 1

, idx, . - , , .

Answer 2

, ROWID, , , ( ).