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

[Kal]

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.

[Steve]

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:

[orderedJoinIDs][VendorID]

See Constructing a List from a List Dereference here:

help.appsheet.com

List Expressions and Aggregates

List expressions use and/or produce lists of values.

[Kal]

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:

Kal:

ORDERBY(SELECT(ProductVendorJoin[JoinID], [_THISROW].[ProductID] = [ProductID]), [Pref], FALSE)

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.