sql - How to: Oracle analytic function to return row

Question

I guess my question could be answered with an Oracle analytic function in the SQL but I am not to sure. Say I have the following "DOCUMENTS" DB Table:

• Rank: Reverse Sequence, each document has its own sequence, latest document revision has lowest (0) number
• Revision: Alpha numerical sequence per document, latest document revision has highest revision id

NAME	RANK	REVISION	STATE
DocumentA	0	5b	ReadOnly
DocumentA	1	5a	Draft
DocumentA	3	3	ReadOnly
DocumentA	4	2	Draft
DocumentA	2	4	Published
DocumentA	5	1	Published
DocumentB	0	2	Draft
DocumentB	1	1	Published
DocumentC	0	1	Published

question from:https://stackoverflow.com/questions/65920546/how-to-oracle-analytic-function-to-return-row

Answer 1

Something like this?

SQL> with test (name, rank, revision, state) as
  2    (select 'A', 0, '5b', 'ReadOnly'  from dual union all
  3     select 'A', 2,  '4', 'Published' from dual union all
  4     select 'A', 5,  '1', 'Published' from dual union all
  5     select 'B', 0,  '2', 'Draft'     from dual union all
  6     select 'B', 1,  '1', 'Published' from dual union all
  7     select 'C', 0,  '1', 'Published' from dual
  8     )
  9  select name, rank, revision, state
 10  from (select t.*,
 11          rank() over (partition by name order by revision desc, rank) rn
 12        from test t
 13        where state = 'Published'
 14       )
 15  where rn = 1;

N       RANK RE STATE
- ---------- -- ---------
A          2 4  Published
B          1 1  Published
C          0 1  Published

SQL>