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

Requested result set: Latest published revision for each document

Give me, for each document, the latest published document, having the lowest rank number

Since the latest document revision can be in state draft it is not always 0

NAME	RANK	REVISION	STATE
DocumentA	2	4	Published
DocumentB	1	1	Published
DocumentC	0	1	Published

Please formulate the SQL query to return this result set. Many thanks!

https://dba.stackexchange.com/questions/6368/how-to-select-the-first-row-of-each-group — Mat, Jan 27 '21 at 13:58
Do you want to get the penultimate rows just before `Draft` whenever descendingly ordered starting from the earliest ones? — Barbaros Özhan, Jan 27 '21 at 14:00
I am interested only in published revisions, no matter how many document revisions have been added before or later — Spanky, Jan 27 '21 at 14:07
You decide for DocumentA RANK 2 REVISION 4, because RANK 2 < RANK 5 or because REVISION 4 > REVISION 1? — Thorsten Kettner, Jan 27 '21 at 14:07
Because rank 2 < rank 5. I want the published revisions having the lowest rank, since that is the newest document revision. — Spanky, Jan 27 '21 at 14:09
So, we can ignore the revision column completely? This would have been easier to understand, had you just omitted it from your request :-) Littlefoot's answer should work for you. Only remove the revision from the order by clause. — Thorsten Kettner, Jan 27 '21 at 14:13
Correct. The revision is not necessary for the query. I added it only to better visualise the reverse character of the rank. — Spanky, Jan 27 '21 at 14:15

score 0 · Answer 1 · answered Jan 27 '21 at 14:00

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>

score 0 · Answer 2 · answered Jan 27 '21 at 14:13

Requested result set: Latest published revision for each document

One method doesn't use window functions:

select t.*
from t
where t.state = 'Published' and
      t.rank = (select min(t2.rank) 
                from t t2
                where t2.name = t.name and t2.state = t.state
               );

And, in Oracle, you can even use aggregation:

select document, state, min(rank),
       min(revision) keep (dense_rank first order by rank) as revision
from t
where state = 'Published'
group by document, state;

Window functions are a very reasonable solution to the problem, however, they are not required.

How to: Oracle analytic function to return row

2 Answers2