sql - 窗口函数的确定性排序顺序

Question

我有一个status 表，我想获取最新的详细信息。

Slno |   ID | Status | date
  1  |   1  | Pass   | 15-06-2015 11:11:00 - this is inserted first
  2  |   1  | Fail   | 15-06-2015 11:11:00 - this is inserted second 
  3  |   2  | Fail   | 15-06-2015 12:11:11 - this is inserted first
  4  |   2  | Pass   | 15-06-2015 12:11:11 - this is inserted second

我使用带有partition by ID order by date desc 的窗口函数来获取第一个值。
异常输出:

2  |   1  | Fail   | 15-06-2015 11:11:00 - this is inserted second
4  |   2  | Pass   | 15-06-2015 12:11:11 - this is inserted second

实际输出:

1  |   1  | Pass   | 15-06-2015 11:11:00 - this is inserted first
3  |   2  | Fail   | 15-06-2015 12:11:11 - this is inserted first

根据 [ http://docs.aws.amazon.com/redshift/latest/dg/r_Examples_order_by_WF.html] ，向窗口函数添加第二个 ORDER BY 列可能会解决问题。但是我没有任何其他列来区分行!
有没有其他方法可以解决这个问题？

编辑:为了清楚起见，我在此处添加了 slno。我在表中没有这样的 slno!

我的SQL:

with range as (
  select id from status where date between 01-06-2015 and 30-06-2015
), latest as ( 
  select status, id, row_number() OVER (PARTITION BY id ORDER BY date DESC) row_num
)
select * from latest where row_num = 1

Answer 1

如果您的表中没有slno，那么您就没有任何可靠的信息哪一行先被插入。表中没有自然顺序，行的物理顺序可以随时更改(通过任何更新，或使用 VACUUM 等)

您可以使用不可靠技巧:按内部 ctid 排序。

select *
from  (
   select id, status
        , row_number() OVER (PARTITION BY id
                             ORDER BY date<b>, ctid</b>) AS row_num
   from   status  -- that's your table name??
   where  date >= '2015-06-01'  -- assuming column is actually a date
   and    date <  '2015-07-01'
  ) sub
where  row_num = 1;

• 在没有任何其他信息的情况下哪一行排在第一位(首先是设计错误，请修复它!)，您可以尝试使用内部 tuple ID ctid 保存您可以保存的内容
• In-order sequence generation

行在最初插入时将按物理顺序排列，但随着对表的任何写入操作或 VACUUM 或其他事件的发生，该顺序随时可能更改。
这是万不得已的措施，它会失效。

• 您提出的查询在几个方面是无效的:第一个 CTE 中缺少列名，第二个 CTE 中缺少表名，...

• 您不需要 CTE。

DISTINCT ON 更简单(ctid 的注意事项同样适用):

SELECT DISTINCT ON (id)
       id, status
FROM   status
WHERE  date >= '2015-06-01'
AND    date <  '2015-07-01'
ORDER  BY id, date, ctid;

• Select first row in each GROUP BY group?