1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

How to: Oracle analytic function to return row

Discussão em 'Outras Linguagens' iniciado por Stack, Janeiro 27, 2021.

  1. Stack

    Stack Membro Participativo

    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!

    Continue reading...

Compartilhe esta Página