Is my subquery inneficient. It is extremely slow
Your query is slow because of the sub-select.  It is what is known as a correlated sub query.  It will be executed once per row in the outer query.

This question answers a similar problem.  It's looking for the minimum, you for the maximum; it's using one table, you're using one.  The translation shouldn't be too hard, though.