sql - 比较PostgreSQL中每个ID的不同行

我的表中有几列看起来像

Id   Code   date        latest
1    T     2014-10-04    0
2    B     2014-10-19    0
2    B     2014-10-26    0
1    S     2014-10-05    0
1    T     2014-10-06    0
1    T     2014-10-08    1
2    P     2014-10-27    1

我正在跟踪每个ID所做的所有更改。如果有任何更改，我将插入新行并更新最新值列。
我想要的是对于每个Id，我应该能够找到最新的0的最后一个代码。同样，该代码不应该等于现有代码（最新＝1），因此对于ID＝1，答案不能

 Id   Code
 1    T

至于id = 1 t是现有代码（latest = 1）。
所以理想情况下，我的输出应该是：

Id    Code
 1     S
 2     B

我想我可以得到每个idlatest = 0的代码的最新值。
但如何确保它不等于现有代码值（latest = 1）

最佳答案

在Postgres工作：

SELECT DISTINCT ON (t0.id)
       t0.id, t0.code
FROM   tbl t0
LEFT   JOIN tbl t1 ON t1.code = t0.code
                  AND t1.id = t0.id
                  AND t1.latest = 1
WHERE  t0.latest = 0
AND    t1.code IS NULL
ORDER  BY t0.id, t0.date DESC;

我使用LEFT JOIN/IS NULL的组合来删除latest = 1的同级行。有多种方法可以做到这一点：
Select rows which are not present in other table
DISTINCT ON的详细信息：
Select first row in each GROUP BY group?
带CTE和2个左连接的版本
因为Redshift似乎不支持DISTINCT ON：

WITH cte AS (
   SELECT t0.*
   FROM   tbl t0
   LEFT   JOIN tbl t1 ON t1.code = t0.code
                     AND t1.id = t0.id
                     AND t1.latest = 1
   WHERE  t0.latest = 0
   AND    t1.id IS NULL
   )
SELECT c0.id, c0.code
FROM   cte c0
LEFT   JOIN cte c1 ON c1.id = c0.id
                  AND c1.date > c0.date
WHERE  c1.id IS NULL
ORDER  BY c0.id;

SQL Fiddle显示两者。