SQL: select max(A), B but don’t want to group by or aggregate B

If I have a house with multiple rooms, but I want the color of the most recently created, I would say:

select house.house_id, house.street_name, max(room.create_date), room.color
from house, room
where house.house_id = room.house_id
and house.house_id = 5
group by house.house_id, house.street_name

But I get the error:

Column ‘room.color’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I say max(room.color), then sure, it will give me the max(color) along with the max(create_date), but I want the COLOR OF THE ROOM WITH THE MAX CREATE DATE.

just added the street_name because I do need to do the join, was just trying to simplify the query to clarify the question..

Answer

Expanding this to work for any number of houses (rather than only working for exactly one house)…

SELECT
  house.*,
  room.*
FROM
  house
OUTER APPLY
(
  SELECT TOP (1) room.create_date, room.color
    FROM room
   WHERE house.house_id = room.house_id
ORDER BY room.create_date DESC
)
  AS room