SQLite SELECT that returns ids and positions

Question

Using a SQLite SELECT statement, I'd like to be able to get the position of each results.

Is that even possible?

Table:

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

Having [fk_id, idx] being unique.

Query:

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

Results:

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

The <??> being the "order"/"position"/"index" information I seek.

Answer 1

SQLite doesn't have analytic support - the closest you can get is to use a subselect:

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

The caveat here is that if you have duplicate idx values, they all get the same position value. The only way with this method to get distinct values, is to add criteria that for logic to distinguish which of the duplicates comes first.

Answer 2

You might be able to get the info from the hidden ROWID column, though that's probably going to be discontinuous (if that matters).