Join tables but allow use of records once only

Question

CREATE TABLE #A (UpperLimit NUMERIC(4))
CREATE TABLE #B (Id NUMERIC(4), Amount NUMERIC(4))

INSERT INTO #A VALUES 
    (1000), (2000), (3000)
INSERT INTO #B VALUES 
    (1, 3100), 
    (2, 1900), 
    (3, 1800), 
    (4, 1700), 
    (5, 900), 
    (6, 800)

Given these 2 tables, I want to join Table A to B ON B.Amount < A.UpperLimit but each record from Table B can only be used once, so the desired output would be:

I could easily do this by plopping Table B's records into a temp table, cursor over table A taking top record < UpperLimit and Deleting that record from the temp table or some other programmatic solution, but I'd like to avoid that and I'm pretty sure this could be done with a "normal" (recursive CTE? Partition?) query.

Answer 1

You could achieve your desired output using below recursive CTE

WITH 
DATA AS
(
  SELECT * FROM #A A1 INNER JOIN #B B1 ON A1.UpperLimit >= B1.Amount
),
MA AS
(
  SELECT MIN(UpperLimit) AS MinLimit, MAX(UpperLimit) AS MaxLimit FROM #A
),
RESULT AS 
(
  -- Get the first record corresponding with maximum upper limit  
  SELECT * 
  FROM DATA D1
  WHERE NOT EXISTS 
        (SELECT 1 
        FROM DATA D2 
        WHERE D2.UpperLimit = D1.UpperLimit AND D2.Amount > D1.Amount)
        AND D1.UpperLimit = (SELECT MaxLimit FROM MA)
  
  -- Recursive get remain record corresponding with other upper limit 
  UNION ALL      
  SELECT D1.* 
  FROM RESULT R1 INNER JOIN DATA D1 
       ON (R1.UpperLimit > D1.UpperLimit AND R1.Id != D1.Id) 
  WHERE D1.UpperLimit >= (SELECT MinLimit FROM MA)
       AND NOT EXISTS 
        (SELECT 1 
        FROM DATA D2 
        WHERE D2.UpperLimit = D1.UpperLimit AND D2.Amount > D1.Amount AND D2.Id != R1.Id)   
)

SELECT DISTINCT * FROM RESULT ORDER BY UpperLimit DESC;

Demo: https://dbfiddle.uk/Y-m0K6Mk