需要一个postgres查询，可以选择最新的版本以及相关的下一个最新

Question

Status       Version  id
Unprocessed  6        abc
Processed    5        abc
Processed    4        abc
Processed    3        abc
Unprocessed  4        abc1
Processed    3        abc1
Processed    2        abc1
Processed    1        abc1
Unprocessed  2        abc2
Unprocessed  1        abc2

从这个我想选择未处理的数据与最新的处理版本。假设我的输出应该是

Unprocessed   6   abc
Processed     5   abc
Unprocessed   4   abc1
Processed     3   abc1
Unprocessed   2   abc2
Unprocessed   1   abc2

我尝试使用延迟和领先的postgres，但它填充延迟和领先的所有行，我不想要的。我找不到合适的where子句。

Answer 1

SELECT status,
version,
id
FROM   mytable
WHERE  status = 'Unprocessed'
UNION
SELECT status,
version,
id
FROM   (SELECT status,
version,
id,
Row_number ()
OVER (
partition BY status, id
ORDER BY version DESC ) RN
FROM   mytable
WHERE  status = 'Processed') A
WHERE  rn = 1
ORDER  BY version DESC  

dbfiddle