Join tables but allow use of records once only-CodePudding

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:

WITH C AS (SELECT Limit,
                  Value,
                  ID,
                  CASE WHEN ROW_NUMBER() OVER (PARTITION BY Value ORDER BY value) = 1 then 'True' END as 'result'
           FROM #A LEFT JOIN #B ON #B.Value < #A.Limit),
     cc AS
         (select min(Limit) OVER ( PARTITION BY Value)  AS 'Limit2',
                 Value,
                 ID,
                 CASE WHEN MAX(VALUE) OVER ( PARTITION BY Limit) = Value THEN 'True' END as 'r2'
          FROM c
          WHERE result IS NOT NULL)
SELECT *
FROM cc
WHERE r2 IS NOT NULL