Select max status from table with same id

Question

I have table

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

Want to retrieve from

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

Based on status preference I need to fetch part no based on status sequence

1. Current
2. Trail

. In Oracle SQL.

Answer 1

One option is as follows (sample data in lines #1 - 6; query begins at line #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>