CREATE TABLE Rooms
(
    room_nbr CHAR(2) NOT NULL PRIMARY KEY
   ,room_size INTEGER NOT NULL CHECK (room_size > 0)
)

CREATE TABLE Classes
(
    class_nbr CHAR(2) NOT NULL PRIMARY KEY
   ,class_size INTEGER NOT NULL CHECK (class_size > 0)
)

INSERT INTO Classes (class_nbr, class_size) VALUES ('c1', 80)
INSERT INTO Classes (class_nbr, class_size) VALUES ('c2', 70)
INSERT INTO Classes (class_nbr, class_size) VALUES ('c3', 65)
INSERT INTO Classes (class_nbr, class_size) VALUES ('c4', 55)
INSERT INTO Classes (class_nbr, class_size) VALUES ('c5', 50)
INSERT INTO Classes (class_nbr, class_size) VALUES ('c6', 40)

INSERT INTO Rooms (room_nbr, room_size) VALUES ('r1', 70)
INSERT INTO Rooms (room_nbr, room_size) VALUES ('r2', 40)
INSERT INTO Rooms (room_nbr, room_size) VALUES ('r3', 50)
INSERT INTO Rooms (room_nbr, room_size) VALUES ('r4', 85)
INSERT INTO Rooms (room_nbr, room_size) VALUES ('r5', 30)
INSERT INTO Rooms (room_nbr, room_size) VALUES ('r6', 65)
INSERT INTO Rooms (room_nbr, room_size) VALUES ('r7', 55)

-- Resolve the requirement to an expression on room sets, smaller_room_size < class_size < bigger_room_size
-- i.e. the set of those rooms that are smaller than the class size joined 
-- with the set of those rooms that are bigger than the class size
-- Straight away the solution is evident...
SELECT *
  FROM Rooms a
       CROSS JOIN
       Classes b
       CROSS JOIN
       Rooms c
 WHERE a.room_size < b.class_size
   AND c.room_size > b.class_size
ORDER BY b.class_size DESC, a.room_size DESC, c.room_size ASC

-- For each class size get the biggest room that is smaller and the smallest room that is bigger...
SELECT MAX (a.room_size) AS just_too_small_room_size
      ,b.class_size
      ,MIN (c.room_size) AS just_too_big_room_size
  FROM Rooms a
       CROSS JOIN
       Classes b
       CROSS JOIN
       Rooms c
 WHERE a.room_size < b.class_size
   AND c.room_size > b.class_size
GROUP BY b.class_size

-- ... and join it back to the solution candidates set, et voila...
SELECT y.class_nbr AS class_nbr_requiring_room
      ,y.class_size AS class_size_requiring_room
      ,y.bigger_room_nbr AS best_fitting_room_nbr
      ,y.bigger_room_size AS best_fitting_room_capacity
  FROM (
         SELECT MAX (a.room_size) AS just_too_small_room_size
               ,b.class_size
               ,MIN (c.room_size) AS just_too_big_room_size
           FROM Rooms a
                CROSS JOIN
                Classes b
                CROSS JOIN
                Rooms c
          WHERE a.room_size < b.class_size
            AND c.room_size > b.class_size
         GROUP BY b.class_size
       ) x
       JOIN
       (
         SELECT a.room_nbr AS smaller_room_nbr
               ,a.room_size AS smaller_room_size
               ,b.class_nbr
               ,b.class_size
               ,c.room_nbr AS bigger_room_nbr
               ,c.room_size AS bigger_room_size
           FROM Rooms a
                CROSS JOIN
                Classes b
                CROSS JOIN
                Rooms c
          WHERE a.room_size < b.class_size
            AND c.room_size > b.class_size
       ) y
          ON x.just_too_small_room_size = y.smaller_room_size
             AND x.class_size = y.class_size
             AND x.just_too_big_room_size = y.bigger_room_size
ORDER BY y.class_nbr

-- Obviously there is contention for the room r4 but that is a different problem.

select c.class_nbr
     , c.class_size  
     , r.room_nbr
     , r.room_size
  from classes c 
  join rooms r
    on c.class_size <= r.room_size
 where (select count(*)
          from classes cc 
          join rooms rr
            on cc.class_size <= rr.room_size
         where cc.class_nbr = c.class_nbr
           and rr.room_size <= r.room_size
         ) = 1
 order 
    by c.class_nbr

select a.class_nbr
     , a.class_size  
     , a.room_nbr
     , a.room_size
 from (
      select c.class_nbr
           , c.class_size  
           , r.room_nbr
           , r.room_size
           , (select count(*)
                from classes cc 
                join rooms rr
                  on cc.class_size <= rr.room_size
               where cc.class_nbr = c.class_nbr
                 and rr.room_size <= r.room_size
             ) qual
       from classes c 
       join rooms r
         on c.class_size <= r.room_size
      ) a
where a.qual = 1
order 
    by a.class_nbr

SELECT
      A.[ROOM_NBR]
     ,A.[ROOM_SIZE]
     ,B.[CLASS_NBR]
     ,B.[CLASS_SIZE]
FROM
      #ROOMS A
      JOIN #CLASSES B
            ON A.[ROOM_SIZE] >= B.[CLASS_SIZE]
WHERE
      B.[CLASS_SIZE] = ( SELECT
                              MAX(C.[CLASS_SIZE])
                          FROM
                              #CLASSES C
                          WHERE
                              C.[CLASS_SIZE] <= A.[ROOM_SIZE] )
ORDER BY
      A.[ROOM_SIZE] DESC
     ,B.[CLASS_SIZE] DESC

ROOM_NBR ROOM_SIZE   CLASS_NBR CLASS_SIZE
-------- ----------- --------- -----------
r4       85          c1        80
r1       70          c2        70
r6       65          c3        65
r7       55          c4        55
r3       50          c5        50
r2       40          c6        40

WITH Matches AS (
		SELECT
			class_nbr,
			class_size,
			room_nbr,
			room_size,
			exact_match = CASE WHEN class_size = room_size THEN 1 ELSE 0 END
		FROM Classes, Rooms
		WHERE class_size <= room_size
	),
Preferences AS (
		SELECT
			class_nbr,
			class_size,
			class_room_pref = ROW_NUMBER() OVER (PARTITION BY class_nbr ORDER BY exact_match, room_size, room_nbr),
			room_nbr,
			room_size,
			room_class_pref = ROW_NUMBER() OVER (PARTITION BY room_nbr ORDER BY exact_match, class_size DESC, class_nbr)
		FROM Matches m
		WHERE NOT EXISTS (
					SELECT 1
					FROM Matches
					WHERE room_nbr = m.room_nbr
						AND class_size > m.class_size
				)
 ),
Final AS (
		SELECT
			class_nbr,
			class_size,
			room_nbr,
			room_size,
			final_pref = ROW_NUMBER() OVER (PARTITION BY class_nbr ORDER BY class_room_pref)
		FROM Preferences p
		WHERE NOT EXISTS (
					SELECT 1
					FROM Preferences
					WHERE room_nbr = p.room_nbr
						AND class_room_pref = room_class_pref
						AND room_class_pref < p.room_class_pref
				)
 )
SELECT
 c.class_nbr,
 c.class_size,
 f.room_nbr,
 f.room_size
FROM Classes c
 LEFT JOIN Final f ON c.class_nbr = f.class_nbr AND f.final_pref = 1
ORDER BY 1

select classes.*,rooms.*
from (
	Select 	min_rm
		,min(class_size) as cls -- Ascending
--		,max(class_size) as cls -- Descending
	from(
		select class_nbr,class_size
			,min(room_size) as min_rm
		from (
			select class_nbr,class_size,room_nbr,room_size
			from classes,rooms
			where (class_size < room_size)
		) as CartesianProduct
		group by class_nbr,class_size
	) as PossibleRoomFits
	group by min_rm
) as Result
inner join classes
	on class_size=cls
inner join rooms
	on room_size=min_rm
order by class_nbr