SQL query for FIFO allocation of items I have a real world business scenario but for the sake of clarity let me rephrase it as a simpler fictional problem.
We have Buyers and Items.
DECLARE @Buyer TABLE 
(
 BuyerId INT IDENTITY(1,1),
 Name NVARCHAR(100),
 Budget INT
);

INSERT @Buyer (Name, Budget) VALUES 
('Anna', 100),
('Brett', 50),
('Conor', 20)

DECLARE @Item TABLE
(
 ItemId INT IDENTITY(1,1),
 Cost INT
);

INSERT @Item (Cost) VALUES (50),(30),(20),(40),(10),(40),(30),(10),(5);

The goal is to craft a query (no loops or cursor) which allocates items to buyers, based on their budget.
The most expensive items should be allocated in priority.
Expected result:
ItemId BuyerName  
1  Anna
4  Anna  
6  Brett
7  NULL  
2  NULL  
3  Conor  
5  Anna  
8  Brett
9, NULL  

From a logical perpective, this is what happens.
First, the items are sorted by descending cost
ItemId Cost  
1   50  
4   40  
6   40  
7   30  
2   30  
3   20  
5   10  
8   10  
9   5  

Then we go through each item and try to allocate it to a buyer that has enough budget left.

Item 1 =&gt; Can be assigned to Anna. Anna's remaining budget is 100 - 50 = 50
Item 4 =&gt; Can be assigned to Anna. Anna's remaining budget is 50 - 40 = 10
Item 6 =&gt; Can be assigned to Brett. Brett's remaining budget is 50 - 40 = 10
Item 7 =&gt; No one has enough budget left
Item 2 =&gt; No one has enough budget left
Item 3 =&gt; Can be assigned to Conor. Conor's remaining budget is 20 - 20 = 0
Item 5 =&gt; Can be assigned to Anna. Anna's remaining budget is 10 - 10 = 0
Item 8 =&gt; Can be assigned to Brett. Brett's remaining budget is 10 - 10 = 0
Item 9 =&gt; No one has enough budget left
Nasty, but will do what you wanted:
DECLARE @Out TABLE (BuyerId int, ItemId int);
DECLARE @BuyerId int, @Budget int, @ItemId int, @Cost int;

WHILE EXISTS(SELECT TOP 1 Budget FROM @Buyer WHERE Budget &gt;= (SELECT MIN(Cost) FROM @Item))
BEGIN
    SELECT TOP 1 @BuyerId = BuyerId, @Budget = Budget
    FROM @Buyer
    WHERE Budget &gt;= (SELECT MIN(Cost) FROM @Item)

    SELECT TOP 1 @ItemId = ItemId, @Cost = Cost 
    FROM @Item
    WHERE Cost &lt;= @Budget
    ORDER BY Cost DESC, ItemId ASC;

    DELETE FROM @Item WHERE ItemId = @ItemId;
    UPDATE @Buyer SET Budget -= @Cost WHERE BuyerId = @BuyerId;
    INSERT INTO @Out VALUES(@BuyerId,@ItemId)
END
SELECT * FROM @Out

UPDATE (2020-10-16):
Based on OPs comment I decided to try to do as much as possible using set based data flow. First of all solution requires to create all possible permutations without duplicates where the order doesn't matter. For that I used a recursive CTE, then out of all possible price sums we needed only these in the Buyers budget, then I ordered them form the most expensive set (as asked by OP) where we want most expensive item first then the most expensive still in the budget and so on. I ended up with having the order column, JSON array for ItemId and all other base data:
;WITH totalCostPermutations AS(
SELECT 
ItemIndex = CAST(CONCAT('&quot;',ItemId,'') as varchar(max)),
X = CAST(ROW_NUMBER() OVER (ORDER BY Cost DESC, ItemId ASC) as varchar(max)),
ItemId, Cost
FROM @Item
UNION ALL
SELECT
ItemIndex = CAST(CONCAT(i1.ItemIndex,'&quot;, &quot;',i2.ItemId) as varchar(max)),
X = CONCAT(i1.X, ROW_NUMBER() OVER (ORDER BY i2.Cost DESC, i2.ItemId ASC)),
i2.ItemId, Cost = (i1.Cost + i2.Cost)
FROM totalCostPermutations i1
JOIN @Item i2 ON 1=1
AND i1.ItemId &lt; i2.ItemId
)
SELECT
RN = ROW_NUMBER() OVER(PARTITION BY b.BuyerId ORDER BY Cost DESC, X ASC),
ItemId_JSON = CONCAT('{&quot;ItemId&quot;:[',ItemIndex,'&quot;]}'), TotalCost = Cost,  b.*
FROM totalCostPermutations c
JOIN @Buyer b ON c.Cost &lt;= b.Budget;

At this stage our data looks like this:

But from this point I can't think of how could this be possible to tell which ItemId have already been &quot;used&quot; and which not, without looping.
Still, from this output it won't be hard to either loop it in T-SQL or via the app.