I've a 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
如表中所示!
My 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