I’m working with an Airtable database that has a row containing 17 different numeric columns. My goal is to identify the 5 highest values from all these columns and display them in a separate output format.
Currently, I can only use the MAX function which returns just the single highest value. I need something similar to Excel’s LARGE function that would let me get the 2nd highest, 3rd highest, and so on.
In PostgreSQL, I managed to solve this using a query with CROSS JOIN LATERAL and window functions like ROW_NUMBER() with ORDER BY to rank the values. The query unpivots all column values, ranks them by size, then filters for positions 1 through 5.
Is there a way to accomplish this same logic in Airtable? I need to transform my single row with 17 columns into a result showing the top 5 values from across all those columns.
Just use the scripting extension if you’ve got pro. Write a quick script that loops through your 17 columns, pushes values to an array, sorts descending, and grabs the top 5. Way simpler than workarounds and runs whenever you need it. I do this for ranking survey scores - works great and doesn’t clutter your base with extra fields or tables.
You can do this with a formula-based approach - no automations or database restructuring needed. Create five separate formula fields using nested MAX and IF functions to exclude previously found values. First field uses MAX across all 17 columns. Second field uses MAX again, but wrap each column in an IF statement that excludes the first max. Keep going for fields three through five, excluding all the previous maximums each time. Yeah, it gets pretty verbose with 17 columns, but it works reliably without external scripts or messy table relationships. I’ve done this for budget allocation analysis when I needed the top spending categories from monthly data. Downside is you’ll need to maintain the formulas if your columns change, but it stays within Airtable’s native functionality and updates instantly when your source numbers change.
Set up an automation that triggers when this field changes. The script splits the string, converts to numbers, sorts them descending, and drops your top 5 values into separate fields.
I did this last year for ranking performance metrics across categories. Runs fast and updates in real time.
Or just export to CSV regularly and handle the ranking in Python, then import back. Takes 2 minutes to set up.
Airtable’s array functions suck, but automations give you that PostgreSQL power you want.
I’ve dealt with this exact thing when tracking feature usage across product modules.
That automation idea works, but here’s an easier route if you don’t want to script. Connect Airtable to Google Sheets through Zapier or Make - set it to trigger when your numbers change.
Google Sheets has the LARGE function built in. Just create formulas like =LARGE(A1:Q1,1) through =LARGE(A1:Q1,5) for your top 5, then push those back to Airtable.
Sounds complicated but it’s maybe 15 minutes to set up. Handles any number of columns without ugly formulas cluttering your base.
Bonus: you can add percentile rankings or other analysis in Sheets, then send whatever metrics you need back for dashboards.
Only catch is there’s a slight delay when data updates, but that’s rarely an issue for business use.
Had the same issue with sales data across multiple product categories. Skip the calculated fields with nested IF statements and MAX functions - it’s a nightmare with 17 columns. Here’s what actually works: restructure your data first. Create a linked table where each numeric value becomes its own record with the original row ID and value. Then you can sort and filter like normal to get your top 5. You’ll add 17 records per original row, but Airtable handles it fine. Automate the record creation and your top values update automatically when source data changes. Way cleaner than complex formulas that break constantly.