Airtable - Finding the highest 5 values across 17 columns

Question

I have a table like this:

I want to capture the top 5 values from those 17 columns and made it turned into a table like this:

I tried to use MAX formula but it only gave the max value. I was expecting to use something like Excel's LARGE function but I couldn't find anything like that.

Also I achieved what I am describing up above in POSTGRESQL with this query:

select t.*, v.*
from t cross join lateral
     (select max(v.col) filter (where seqnum = 1) as max_col_1,
             max(v.col) filter (where seqnum = 2) as max_col_2,
             max(v.col) filter (where seqnum = 3) as max_col_3,
             max(v.col) filter (where seqnum = 4) as max_col_4,
             max(v.col) filter (where seqnum = 5) as max_col_5
      from (select v.col, row_number() over (order by col desc) as seqnum
            from (values (col_1), (col_2), . . .
                 ) v(col)
           ) v
      ) v;

I don't know how to do this in airtable.

Answer 1

You'll likely need to dip into your javascript bag to accomplish this. Here is an example script using the Automations feature, where for each row 1) grab the values 2) sort them appropriately 3) choose the top5 4) place them back in the row

// use the `+ Add input variable` to add your 17 columns
// store them as a const
const PARAMS = input.config()

// get each column's value and order them from largest to smallest
// using .sort & .reverse
let orderedSort = Object.values(PARAMS).sort().reverse()
//console.log(orderedSort)

// output each including any error correction for ties or nulls
output.set('max1', orderedSort[0])
output.set('max2', orderedSort[1])
output.set('max3', orderedSort[2])
output.set('max4', orderedSort[3])
output.set('max5', orderedSort[4] || '')

The next action in the Automation is to update the record by mapping the 'outputs' from the script action.