How to extract top 5 largest values from multiple columns in Airtable

Other Questions Airtable
1

I’m working with an Airtable database that has a record containing 17 different numeric columns. What I need to do is identify the 5 highest values from all these columns combined and display them in a structured format.

Currently I can only get the maximum value using the MAX function, but I need something similar to Excel’s LARGE function to get the top 5 values ranked in order.

In PostgreSQL I was able to solve this using a query with lateral joins and window functions:

select data.*, results.*
from data cross join lateral
     (select max(val.number) filter (where rank_num = 1) as top_value_1,
             max(val.number) filter (where rank_num = 2) as top_value_2,
             max(val.number) filter (where rank_num = 3) as top_value_3,
             max(val.number) filter (where rank_num = 4) as top_value_4,
             max(val.number) filter (where rank_num = 5) as top_value_5
      from (select val.number, row_number() over (order by number desc) as rank_num
            from (values (field_1), (field_2), (field_3)
                 ) val(number)
           ) val
      ) results;

But I can’t figure out how to achieve the same result in Airtable. Is there a way to rank and extract multiple top values across several columns?

2

Airtable doesn’t have built-in functions for cross-column ranking. I hit the same issue building a performance dashboard last year.

Here’s what works:

Make separate formula fields for each rank. For the highest value:

MAX(Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13, Field14, Field15, Field16, Field17)

For second highest, exclude the first max:

MAX(
IF(Field1 = {Top Value 1}, 0, Field1),
IF(Field2 = {Top Value 1}, 0, Field2),
...
)

This gets messy with 17 columns though.

Better option: use Airtable’s scripting block or automation. Write a script that reads all 17 values, sorts them, and dumps the top 5 into separate fields.

let values = [record.getCellValue('Field1'), record.getCellValue('Field2'), /* ... */];
let sorted = values.sort((a, b) => b - a);
let top5 = sorted.slice(0, 5);

I’ve done this multiple times when Airtable formulas can’t handle it. The script runs when source data changes and keeps your top 5 updated automatically.