Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Dataflows (How to use RANK function in descending order?)

Accepted answer
25
Views
9
Comments
Jestin Rajan
Jestin Rajan Rank 3 - Community Apprentice
edited 10:11AM in Oracle Analytics Cloud and Server

Is there a way to use the RANK function in descending order in Oracle Analytics?

Currently, it ranks the latest date as Rank 1 (and older dates get higher rank numbers). However, I’d like to reverse this order so that the latest date gets the lowest rank instead.

I know this can be achieved through aggregation, but I wanted to check if the RANK function itself supports reversing the order (i.e., ranking in ascending instead of descending).

Thanks in advance!

image.png
Tagged:

Best Answer

  • Gianni Ceresa
    Gianni Ceresa Mod
    edited 2:44PM Answer ✓

    You have an "annoying" data type for this… (a date, with a number it is a bit "lighter").

    The LSQL rank function works only in that way, in descending order, and doesn't have any flag or attribute to change the behaviour (at least nothing that has been documented in the past 15+ years).

    Therefore you need to cheat, and use a calculation that reverse the order of the value you rank.

    In your case you can take your date and calculate the number of days between that and a date far away in the future. That number will now rank "ascending": the oldest date will rank 1.

    It does the job, it isn't the fastest way but it does the job. But if you are in a data flow, the result will be stored, therefore performance isn't really your problem.

Answers

  • Brendan T
    Brendan T Rank 6 - Analytics Lead

    Try adding an ORDER BY RANK(CAST(Month AS DATE) ORDER BY date ASC) or

    ORDER BY RANK(CAST(Month AS DATE) ORDER BY date DESC)

  • Jestin Rajan
    Jestin Rajan Rank 3 - Community Apprentice

    Hi,

    Thank you for looking into this. I just gave it a try, but I’m getting the following error:

    Near <ORDER>: Syntax error [nQSError: 26012]

    Any idea what might be causing this?

    Thanks again for your help!

  • Brendan T
    Brendan T Rank 6 - Analytics Lead

    Have a look at the documentation for RANK https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/RANK.html

    See what works for you. Another alternative

    RANK() WITHIN GROUP (ORDER BY month DESC)

  • Jestin Rajan
    Jestin Rajan Rank 3 - Community Apprentice

    I think Data Flows internally use Logical SQL, and as far as I know, it doesn’t support the ORDER BY function directly — which might be why it’s not working as expected.

  • Gianni Ceresa
    Gianni Ceresa Mod

    Do you need RANK to start with 1 or you just need the order given by RANK? This depends on your planned usage of that column value…

  • Jestin Rajan
    Jestin Rajan Rank 3 - Community Apprentice

    Hi @Gianni Ceresa,
    Thanks for checking — yes, I’d like the RANK to start with 1, as my intent is to filter on Rank = 1 to keep only the rows with the earliest date.

  • Gianni Ceresa
    Gianni Ceresa Mod

    You could also consider BottomN, but that's an aggregation function and because it does remove the records not matching the "n", no idea how it will behave in a data flow, and you maybe don't want to lose the other records.

    Also keep in mind RANK assign the same ranking to the same value, you will still need to handle multiple records having the same ranking yourself by ranking based on something else…

  • Jestin Rajan
    Jestin Rajan Rank 3 - Community Apprentice
    https://community.oracle.com/products/oracleanalytics/discussion/comment/71396#Comment_71396

    Thank you for sharing both the approaches! I’ll go with the first one — the workaround where you calculate the difference between the earliest and the latest date before applying the rank. I think this should address what I was trying to achieve in data flows.

    Appreciate your help!