Airtable - How to Retrieve the Top 5 Values from 17 Different Columns?

Other Questions Airtable
1

I have a dataset structured with multiple columns, and I need to determine the highest five values from 17 columns displayed in a table format. I attempted using the MAX function; however, it only returns the single highest value. I am looking for a method similar to Excel’s LARGE function, but I’m unable to find an equivalent in Airtable. Previously, I achieved this using PostgreSQL with the following 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;

Can anyone guide me on how to replicate this functionality in Airtable?

2

If you’re looking for a similar approach to Excel’s LARGE function in Airtable, consider using a combination of formulas and calculated fields indirectly. One workaround is extracting data using the Rollup field with the ARRAYJOIN function to bring together all column values into a single cell. From there, utilize scripting blocks (if you are on a Pro plan), where you can write custom JavaScript code to replicate the selection of top values. This approach demands a bit more manual setup but can effectively mirror the SQL solution in handling your requirement within Airtable’s constraints.