Calculate rank values of entire data source

[FishTacos]

Hello- I am trying to calculate 3 rank values for my data source.  1- overall rank, 2-gender rank, 3-age rank.  I have a tabular report displaying each individuals record with a time for a run.  Each person needs to have an overall rank, gender rank and age rank.  I can accomplish this with a calculated field using RANK functions by each respective data field but my problem is that if you change search criteria it will change the rank.  I want the ranks to remain constant regardless of the search filters...almost as if those rank values live on the database table.  I have tried to move this from a datapage calculation field to a database trigger to insert the values on the database table but can't seem to get any logic how I want it in the trigger expression builder - is it even capable of doing this?

Example of gender rank would be: DENSE_RANK () OVER (PARTITION BY [@field:cb_vr_results_SEX] ORDER BY [@field:cb_vr_results_TIME]).  Assigns gender place by time ASC for each M and F.

Any help or leads would be helpful.

Thank you!

[Nuke354]

Based from my experience, some SQL functions such as DENSE_RANK () are not available within the Caspio Platform. Have you tried using the same OnLoad and OnExit parameters when searching?

Perhaps you can give us a sample test DataPage or some screenshots on how you configured it so we can check.

[TellMeWhy]

Instead of using {@field:field] have you tried the manual SELECT Statement?

I believe if you used field parameters, it will abide by the rules of the DataPage, so, if it's filtered, it will only check the filtered. If you use SELECT Statement, it will take from the Table itself regardless of the filtered set in the DataPage

[FishTacos]

2 hours ago, TellMeWhy said:

Instead of using {@field:field] have you tried the manual SELECT Statement?

I believe if you used field parameters, it will abide by the rules of the DataPage, so, if it's filtered, it will only check the filtered. If you use SELECT Statement, it will take from the Table itself regardless of the filtered set in the DataPage

It doesn't seem to work.  Keep getting formula errors.  This is a good suggestion and I was hopeful.  Below is what I was using:

 DENSE_RANK () OVER (PARTITION BY (SELECT cb_vr_results_SEX FROM view_cb_vr_results WHERE cb_vr_results_DISTANCE_SUBMITTED=[@DISTANCE_SUBMITTED])
 ORDER BY (SELECT cb_vr_results_TIME FROM view_cb_vr_results WHERE cb_vr_results_DISTANCE_SUBMITTED=[@DISTANCE_SUBMITTED]))

[@DISTANCE_SUBMITTED] is a param passed from Search.  

Am I missing something?

[futurist]

Hi @FishTacos,

this might be an alternative that you might wanna consider: 

 

[DrSimi]

Hi,

I just wanted to share a couple of notes here, it is possible to implement RANK, ROW_NUMBER and DENSE_RANK functions in Triggered Actions or Tasks without the actual function since it is not available. See this Stackoverflow post:

https://stackoverflow.com/a/46856508 

Db Fiddle if you want to play around with pure SQL: https://dbfiddle.uk/wrHuSG5a 

RANK in Task:

DENSE_RANK in Task:

[cheonsa]

Hi all,

Just wanted to share with you the three formulas to use when ranking records. These are working in Calculated Fields in the DataPage.

RANK() 
- RANK() SQL Rank function is used to specify the rank for each row in the result set.
- Treats ties as equal but ranks will be skipped with each change in value.

RANK() OVER (PARTITION BY Request_No ORDER BY Date DESC)

ROW_NUMBER() 
- ROW_Number() SQL RANK function is used to get a unique sequential number for each row in the specified data. It gives the rank one for the first row and then increments the value by one for each row. We get different ranks for the row having similar values as well.
- Does not consider ties.

ROW_NUMBER() OVER (PARTITION BY Request_No ORDER BY Date DESC)

 

DENSE_RANK() 
- DENSE_RANK() function is used to specify a unique rank number within the partition as per the specified column value
- Treats ties as equal but ranks are not skipped with each change in value

DENSE_RANK() OVER (PARTITION BY Request_No ORDER BY Date DESC)

Please note to change the Request_No and Date fields.