# Comparing records in different fields in the same table

Hi, I have 3 Fields “Name” “Results” “Position”

I will like to compare the values in “Results” Field and position it from ‘1st - last row’

(Field “Position”)
If “Field Results” Row 1 or Record 1 is > Row 2, then Row 1 = “1st”
else If
“Results” Record 2 > Record 3, then Row 2 = “2nd”
else if
“Results” Record 2 = Record 1, then Row 2 = “1st”

What I want the “Position” Field to do is, compare if Results Field Row 1 is Greater than Row 2 then Position Field Row 1 is equal to “1st”

And if Results Field Row 2 is equal to Row 1 then Position Row 2 will be “1st”
Or if
Row 2 > row 3 then Position row 3 = “3rd”

Finally if “Results” Row 1 = Row 2 then both "Position Row 1 and Row 2 will be “1st”
But “Position” Row 3 will be “3rd”

What formula do I use to achieve this? Help me people

This is what I want to Achieve

This can’t be done with a formula alone. Formulas can only reference data in the record to which that formula is applied. You could do one of several things including:

1. Use an automation with a script (see example from the podcast linked below)
[S09-E07] Full Podcast for 10-26-2021 [RAW ORIGINAL VIDEO] - YouTube

The improved version of the script discussed in that episode is copied below. Since you seem to want absolute ranking (i.e. no grouping), you can set your `let groupPath = []`

``````/*

SCRIPT SETTINGS

Edit the values in quotes below to match your base's structure

*/

// table containing the records to be ranked

let tableName = "Table name"

// view which groups the records to be ranked, this view should be in the desired sort order

let viewName = "Name of sorted view"

// names of each field by which the view is grouped ~in the correct order~

let groupPath = ["Grouped field 1", "Grouped field 2"]

// Number-type field that contains the ranking

let rankFieldName = "Position"

/*

QUERIES

Do not edit anything below this line

*/

let table = base.getTable(tableName)

let view = table.getView(viewName)

let fieldsToQuery = [...groupPath, rankFieldName]

let query = await view.selectRecordsAsync({fields: fieldsToQuery})

let records = query.records

let groups = []

records.forEach(record => {

let groupName = groupPath.map(fieldName => record.getCellValueAsString(fieldName)).join("")

let groupIndex = groups.findIndex(x => x.groupName === groupName)

if(groupIndex >= 0) {

groups[groupIndex].recordIds.push(record.id)

} else {

groups.push({groupName: groupName, recordIds: [record.id]})

}

let rank = (

groupIndex >= 0 ?

groups[groupIndex].recordIds.length :

1

)

})