I’m working with an Airtable base that has 17 different columns with numerical values. I need to find the 5 largest values from all these columns combined and display them in a separate table format.
Currently, I can only retrieve the maximum value using the MAX function, but I need something akin to Excel’s LARGE function to get the top 5 values. Is there a way to achieve this in Airtable?
I managed to resolve this issue using PostgreSQL with a query that implements cross join lateral and row_number() functions:
select main.*, results.*
from main_table cross join lateral
(select max(vals.value) filter (where rank_num = 1) as top_value_1,
max(vals.value) filter (where rank_num = 2) as top_value_2,
max(vals.value) filter (where rank_num = 3) as top_value_3,
max(vals.value) filter (where rank_num = 4) as top_value_4,
max(vals.value) filter (where rank_num = 5) as top_value_5
from (select vals.value, row_number() over (order by value desc) as rank_num
from (values (field_1), (field_2), (field_3)
) vals(value)
) vals
) results;
However, I can’t determine how to replicate this functionality within Airtable’s formula system. Any suggestions would be appreciated!