Android Room中如何分组取最大Id？求PARTITION BY替代方案

我有一个名为Diag的SQL表，结构及数据如下：

希望获取每个idDossier对应的最大Id，预期结果如下（注：原预期结果中2231070对应的Id应为4，原表数据中该分组最大Id为4，可能是笔误）：

我在DB Browser for SQLite中通过以下查询实现了需求：

WITH cte AS
(SELECT idDossier, id, ROW_NUMBER() OVER (PARTITION BY idDossier ORDER BY id DESC) as rn FROM old_diagnostic)
SELECT * FROM cte
WHERE rn = 1

但在Room中执行时出现错误：

android.database.sqlite.SQLiteException: near "(": syntax error (Sqlite code 1 SQLITE_ERROR)

原因是PARTITION BY在当前Room环境下无法正常工作。因此我想了解：

1. 以下伪代码的实现方式是否可行？

values = SELECT DISTINCT(idDossier) FROM Diag

foreach(x in values){
  SELECT *, MAX(id) FROM Diag where idDossier = x
}

2. 如果不可行，在不使用PARTITION BY的情况下，该如何实现需求？

伪代码方式的可行性 #

这种方式逻辑可行但不推荐。虽然能得到结果，但存在明显弊端：

• 性能低下：需要对每个idDossier执行一次查询，数据量越大，查询次数越多，效率越低。
• 代码冗余：每次查询返回独立结果集，需要在代码层手动合并结果，增加开发复杂度。

无需PARTITION BY的替代方案 #

方案1：GROUP BY 聚合查询（推荐）

这是最直接高效的方案，利用SQL的分组和聚合函数实现：

SELECT idDossier, MAX(Id) AS Id
FROM Diag
GROUP BY idDossier

该查询按idDossier分组，直接计算每组的最大Id，性能最优，且完全兼容Room环境，执行后会返回符合预期的结果。

方案2：关联子查询（适用于需要完整行数据的场景）

如果需要获取每组最大Id对应的完整行数据（假设表中有更多字段），可以使用关联子查询：

SELECT d.*
FROM Diag d
WHERE d.Id = (
    SELECT MAX(Id)
    FROM Diag
    WHERE idDossier = d.idDossier
)

这种方式会返回每个idDossier分组中Id最大的那一行的所有字段，适合需要更多数据的场景。

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