December 17, 2023 - u/sigcub

Is there a way to calculate ranks for different rows in a database? E.g. based on score column. I'm trying to figure out relations and rollups and whether they could be used for this, but it's still a little confusing.

col1	score	rank
A	34	3
B	100	1
C	54	2

I don't think any of the auto row numbering solutions will work for this.

Quick answer

<aside> <img src="https://prod-files-secure.s3.us-west-2.amazonaws.com/9210293a-6b0f-4638-8e8d-3c40b40ed42b/b8366055-a446-4861-aa67-1e926814ca74/Invisible.png" alt="https://prod-files-secure.s3.us-west-2.amazonaws.com/9210293a-6b0f-4638-8e8d-3c40b40ed42b/b8366055-a446-4861-aa67-1e926814ca74/Invisible.png" width="40px" />

To calculate the rank of a row in a database, you need to be able to compare the values of the different rows. It is therefore essential to link them together. To do this, we use a relation. There are two possible scenarios: I. linking to another database, or II. linking to the same database.

The relation property will be bidirectional. You must ensure that each entry (and each new entry) is linked to a single entry (which I have called "Data Collection").

In case I., we need to create a new formula property "Values (reverse order)" in the second database "Data Collection DB", which will contain all the reverse-ordered values.

prop("Data").map(current.prop("Value")).unique().sort().reverse()

Data Collection DB

This list of values is retrieved from the initial database and will be used to find the rank of each row (by searching for the position of the value associated with the row in the list of values).

let(
	valuesByReverseOrder , prop("Data Collection").map(current.prop("Values (reverse order)")).flat(),
	valuesByReverseOrder.findIndex(current == prop("Value")).add(1)
	)

Data DB

In case II. everything happens in a single formula property: if the entry is named "Data Collection", then the formula generates the list of values, otherwise it determines the rank by searching for the position of the value associated with the row in the list of values.

prop("Name") == "Data Collection" ?

	prop("Data").map(current.prop("Value")).unique().sort().reverse()
	/* If "Data Collection" row, list all values in reverse order */
	
	:
	let(
	valuesByReverseOrder , prop("Data Collection").map(current.prop("Rank")).flat(),
	valuesByReverseOrder.findIndex(current == prop("Value")).add(1)
	)
	/* else use the list of values (reverse order) to find the rank of the row */

Data & Data Collection

Please note:

• The prop("Rank") property calls itself. From the formula editor, the option to select the property (itself) does not exist, but you can type it using the keyboard.
• If two values are equal, they will have the same rank. </aside>

By @Ouinx2 - **Buy me a Beer**