Oracle: MAX of SUM of Each Group

Question

My scenario is to show the hotel room with the highest maintenance cost for each hotel branch by using subqueries. I have three separate tables: branch, room, and maintenance.

Table branch

id             NUMBER(3) PRIMARY KEY
location       VARCHAR2(20)

Table room

id             NUMBER(3) PRIMARY KEY
room_number    CHAR(4)
branch_id      NUMBER(3)

Table maintenance

id             NUMBER(3) PRIMARY KEY
room_id        NUMBER(3)
cost           NUMBER(4)

With my desired output being in the format

location | room_number | cost
-------------------------------
         |             |
         |             |
         |             |

I'm not sure how to select the max value per branch after adding the total costs of each room. Please advise.

Answer 1

You can use window functions:

select *
from (
    select b.location, r.room_number, m.cost, 
        rank() over(partition by b.id order by m.cost desc) rn
    from branch b
    inner join room r on r.branch_id = b.id
    inner join maintenance m on m.room_id = r.id
) t
where rn = 1

If a room might have several maintenances, then we need aggregation:

select *
from (
    select b.location, r.room_number, sum(m.cost) as cost, 
        rank() over(partition by b.id order by sum(m.cost) desc) rn
    from branch b
    inner join room r on r.branch_id = b.id
    inner join maintenance m on m.room_id = r.id
    group by b.id, b.location, r.room_number
) t
where rn = 1