Categories
- All Categories
- Oracle Analytics Learning Hub
- 19 Oracle Analytics Sharing Center
- 19 Oracle Analytics Lounge
- 232 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.9K Oracle Analytics Forums
- 6.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 86 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
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!
Best 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.
3
Answers
-
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)
1 -
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!
0 -
Have a look at the documentation for RANK
See what works for you. Another alternative
RANK() WITHIN GROUP (ORDER BY month DESC)
1 -
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.
2 -
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…
1 -
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.0 -
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…
2 -
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!
1
