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:
[orderedJoinIDs][VendorID]
See Constructing a List from a List Dereference here:
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.
User | Count |
---|---|
35 | |
35 | |
27 | |
23 | |
18 |