Help! How to sort rows by column and return a non keyed column

I have a table with JoinID (key); ProductID (ref); VendorID (ref) and Pref (Number from 1-10).
I’d like to return the VendorID’s according with the lowest Pref value first, but only of a certain ProductID.
Below works (sorts) but returns the key, I can’t get the VendorID from it without losing the sorted order.
ORDERBY(SELECT(ProductVendorJoin[JoinID], [_THISROW].[ProductID] = [ProductID]), [Pref], FALSE)
I’d like something like below, but now Orderby can’t find Pref as it is in the VendorID table.
ORDERBY(SELECT(ProductVendorJoin[VendorID], [_THISROW].[ProductID] = [ProductID]), [Pref], FALSE)
I’m probably missing something real small, but it’s been hours. Please help if you can.

The only way I’m aware of to do what you want is to store the ordered JoinID values in an intermediate column (e.g., orderedJoinIDs), the dereference that list, like:


See Constructing a List from a List Dereference here:

1 Like

I can see how to do this in Google Sheets for example, but how can I create the orderedJoinIDs column in an SQL database, if I use my first statement:

That produces a list of VendorIDs, I can use Index with [_RowNumber] but that only works for the first product and is I believe bad practice.