Oracle SQL*Plus to get the highest Cost_Maint value of each DISTINCT Branch_Id Value

Question

I am trying to generate a report of which room in each branch have the highest cost_maint in Oracle RDBMS. Is it true that this can only be done by self joining table, is it possible to achieve the result by using subquery?

Maintenance_Record Table:

ROOM_NO    BRANCH_ID  COST_MAINT
---------- ---------  ----------
         1 B001      23500
         2 B001       3750
         2 B001       4000
         3 B001      23000
         5 B001       5300
         9 B001      25000
        43 B002       2500
        44 B002       5300
        48 B002       3750
        49 B002       3000
        49 B002       3750
        53 B003       4000
        56 B003       4000
        68 B003       4000
        75 B003      25000
        85 B004       5800
        86 B004       3000
       120 B005      25000

Expected Output / Report :

 ROOM_NO    BRANCH_ID  COST_MAINT
---------- ---------  ----------
         9 B001      25000
        44 B002       5300
        75 B003      25000
        85 B004       5800
       120 B005      25000

Answer 1

You can use row_number()

select * from
(
select room_no,branch_id, const_maint, row_number() over(partition by branch_id order by const_maint desc) as rn
from tablename
)A where rn=1

Answer 2

In Oracle, I would recommend aggregation and keep():

select 
    max(room_no) keep(dense_rank first order by cost_maint desc) as room_no,
    branch_id,
    max(cost_maint) as cost_maint
from mytable
group by branch_id

Demo on DB Fiddle:

ROOM_NO | BRANCH_ID | COST_MAINT
------: | :-------- | ---------:
      9 | B001      |      25000
     44 | B002      |       5300
     75 | B003      |      25000
     85 | B004      |       5800
    120 | B005      |      25000