Sorting Arrays and Retrieving Top Ranked Values in SQL

·Jul 02, 2025 05:46 PM·

Hey there -- I’m looking up records in another table that each have a ranked score (1, 2, 3, 4, 5, etc.). When pulling multiple rows from that table, I need to sort the resulting array by this ranking and return only the row with a score of 1. Is it possible to sort within an array and return just one value?

2 comments

· Sorted by Oldest

Clay
APP
·
·
Yes, you can achieve this using the "Filter List of Objects" integration to filter by your ranking field, or the "Order a list" integration to sort your array by the ranking values. After sorting, you can extract just the first item (with score of 1) from the resulting array. Alternatively, use "Lookup Rows" with a filter operator to directly find rows where the score equals 1.
Sources: Filter List of Objects with Clay Order a list with Clay Lookup Rows | Documentation | Clay University Comparison operators | Documentation | Clay University
Kyle P.
·
·
This is resolved based on the automated Clay-bot response!

Clay
APP
·
·
Yes, you can achieve this using the "Filter List of Objects" integration to filter by your ranking field, or the "Order a list" integration to sort your array by the ranking values. After sorting, you can extract just the first item (with score of 1) from the resulting array. Alternatively, use "Lookup Rows" with a filter operator to directly find rows where the score equals 1.
Sources: Filter List of Objects with Clay Order a list with Clay Lookup Rows | Documentation | Clay University Comparison operators | Documentation | Clay University
Kyle P.
·
·
This is resolved based on the automated Clay-bot response!