安卓低版本SQLite(无ROW_NUMBER)查询指定id排序结果索引

补充说明：我使用的安卓版本搭载的SQLite版本不高于2.35，无法使用ROW_NUMBER窗口函数。

现有测试表结构与数据如下：

id, date(long)
1, 100
2, 25
3, 5
4, 50

执行排序查询select * from items order by date，得到的排序结果为：

id, date
3, 5
2, 25
4, 50
1, 100

需求：已知id值为4，通过SQL查询得到该id对应记录在排序后列表中的索引，本示例中对应索引为3。

低版本SQLite没有窗口函数支持时，用计数法即可实现需求：统计按排序规则排在目标记录之前的条目总数，再加1就是从1开始计数的索引值，结果和排序查询的实际顺序完全对齐。

无重复date值的最简写法 #

如果业务中date字段不会出现重复值，直接用嵌套查询统计比目标记录date值小的条目数即可，语句最简洁：

SELECT COUNT(*) + 1 AS sort_index
FROM items
WHERE date < (SELECT date FROM items WHERE id = 4);

在示例数据中执行该语句，比id=4对应date值（50）小的记录共2条，加1后返回结果3，完全符合预期。

兼容重复date值的稳定写法 #

如果date字段可能存在重复值，需要把排序规则补全为和实际查询的排序逻辑完全一致（比如同date值时按id升序兜底排序），避免同值场景下索引计算错误：

-- 排序逻辑与 ORDER BY date ASC, id ASC 完全对齐
SELECT COUNT(*) + 1 AS sort_index
FROM items
WHERE 
  date < (SELECT date FROM items WHERE id = 4)
  OR (
    date = (SELECT date FROM items WHERE id = 4)
    AND id < 4
  );

该写法兼容所有低版本SQLite，不需要依赖任何高版本特性，计算出的索引值和全量排序后手动计数的结果完全一致。

内容的提问来源于stack exchange，提问作者lostintranslation