Search code examples
sqloracle-databasetop-n

display the cab with the highest overall maintenance cost

I'm not sure how to get the max of the sum. I thought i could just display it in descending order and then use "rownum=1" but that didnt work. Any suggestions? Here's my code.

select ca_make,  sum(ma_cost)
from cab join maintain on ca_cabnum = ma_cabnum 
Where rownum =1
group by ca_make
order by sum(ma_cost) desc
Solution

  • ROWNUM() is applied before the ORDER BY. You need to use a sub-query:

    select * from (
    select ca_make,  sum(ma_cost)
    from cab join maintain on ca_cabnum = ma_cabnum 
    group by ca_make
    order by sum(ma_cost) desc
    )
where rownum = 1

    There are several different ways of implementing [top-n] queries in Oracle. Find out more by searching SO for [oracle] [top-n].