Oracle Communities

Oracle Analytics and AI

Child Item

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

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!

Find more posts tagged with

Oracle Fusion Analytics

Data Preparation and Flows

FDI

Oracle Analytics Cloud

Accepted answers

Gianni Ceresa

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.

All comments

Brendan T

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

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

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

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

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

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

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.

Gianni Ceresa

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

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!