I’m currently using an Airtable table that contains 17 columns filled with numeric data. My goal is to find the top 5 values from these columns and present them in a structured format.
I attempted to use the MAX function available in Airtable, but it only provides me with the highest single value instead of what I need. I am looking for a method akin to Excel’s LARGE function that can help me retrieve the 2nd, 3rd, and 4th highest values as well.
As a point of reference, I successfully implemented this in PostgreSQL with a query that incorporates cross joins and assigns ranks:
select table.*, results.*
from table cross join lateral
(select max(values.column) filter (where rank = 1) as highest_1,
max(values.column) filter (where rank = 2) as highest_2,
max(values.column) filter (where rank = 3) as highest_3,
max(values.column) filter (where rank = 4) as highest_4,
max(values.column) filter (where rank = 5) as highest_5
from (select values.column, row_number() over (order by column desc) as rank
from (values (column_1), (column_2), . . .
) values(column)
) values
) results;
I am unsure how to replicate this in Airtable. Can anyone provide advice?
Honestly, the easiest fix I’ve found is using Zapier or Make.com to push your Airtable data into something that handles this better. Airtable wasn’t built for complex ranking - it’s more a fancy database than spreadsheet. You could try creating a view that groups by your numeric columns, but that’ll be super clunky with 17 fields.
The formula approach could work if you’re okay with nested IF statements. I did something similar - created 5 separate formula fields that basically hardcode the ranking logic. For the highest value, use MAX across all columns. For second highest, use MAX but exclude the first result with conditional logic. Gets messy fast though - you’ll need increasingly complex formulas for each rank. Better option: restructure your data. I converted my wide table to long format where each numeric value became its own record with a link back to the original. Then you can use Airtable’s native sorting and grouping to get your top 5 easily. More work upfront but way cleaner for ongoing analysis and reporting.
The scripting approach is solid, but I’d push this to Make.com instead of forcing Airtable to do something it wasn’t designed for.
I hit this exact problem last year with a project that needed performance metrics ranked across multiple columns. Airtable’s formula system just isn’t built for this - you’ll end up with unmaintainable formulas.
We set up a Make.com scenario that pulls the data, processes it with simple JavaScript, and pushes results back. You can sort your array, grab the top 5, and include which column each value came from. Takes maybe 30 minutes to set up and runs automatically.
Bigger benefit: you can easily modify the logic later. Want top 10 instead of 5? Change one number. With nested Airtable formulas, you’d rewrite everything.
If you absolutely need to stay in Airtable, the script extension works but you’ll manually trigger it every time your data changes. Make.com handles automation way better.
Airtable doesn’t have anything like Excel’s LARGE function or PostgreSQL’s ranking - the built-in functions are pretty limited for what you’re trying to do.
I had a similar project and ended up using Airtable’s scripting feature. Just write a JavaScript script that grabs all 17 columns for each record, throws the values into an array, sorts them high to low, and pulls the top 5. Then populate separate fields with those values.
Another option I’ve seen is exporting to Google Sheets or Excel, running the calculations there with proper ranking functions, then importing back. Not pretty, but works if you don’t need real-time updates.
I’d go with the script approach - keeps everything in Airtable and you can automate it to run when records update.
