从具有相同id的表中选择最大状态

Question

我有桌子

PartNo| Revision Status
501.   1.       Current
501.   2.       Internal
502.   1.       Internal
502.   2.       Trail

想要从

1. PartNo 501 having status is current 
2. Part 502 having status is Trail

基于状态偏好，我需要根据状态序列获取部件号

1. Current
2. Trail

。在Oracle SQL中。

Answer 1

一种选择如下(第1-6行中的样本数据；查询从第7行开始)：

SQL> with test (partno, revision, status) as
  2    (select 501, 1, 'current'  from dual union all
  3     select 501, 2, 'internal' from dual union all
  4     select 502, 1, 'internal' from dual union all
  5     select 502, 2, 'trail'    from dual
  6    )
  7  select partno, revision, status
  8  from (select partno, revision, status,
  9          row_number() over (partition by partno order by revision desc) rn
 10        from test
 11       )
 12  where rn = 1;

    PARTNO   REVISION STATUS
---------- ---------- --------
       501          2 internal
       502          2 trail

SQL>