Finding a max in a list

[Tekk]

Ok the question is that Im to find the room that has created the most revenue from services. In other words: Which room has accumulated the highest amount of charges for various services.

What Im wondering is if this is an effective way of finding the max in a group. Here's my SQL (This is Oracle btw)

Select Room_Number, SUM(Charge_For_Service)
From Service_Performed
Group By Room_Number
Having SUM(Charge_For_Service) >= ALL(Select SUM(Charge_For_Service)
                                      From Service_Performed
                                      Group By Room_Number);

It works just fine. It compares the SUM(Charge_For_Service) to itself and finds the MAX basically. I've tried figuring out a way to use the MAX function but you cant do a function on a function unfortunately i.e. MAX(SUM(Charge_For_Service)).

Opinions? Suggestions? Like I said it works which Im happy but I just wanna make things efficient so I can know what to do next time.

[Pexster]

You could also create a view or temp table and query it, or write it in PL/SQL. If this is something that would be queried all the time, and the table gets large, those might be better options.

But that query is really creative, and as you said, it works!

[Creosote]

The approach I would use would be to limit the rowcount. I'm pretty much a SQL Server and DB2 type of guy, but you can do it in Oracle as well. The idea would be to order your resultset by the sum descending, and only return the first row.

[Pexster]

In Oracle, the psuedo-column ROWNUM can be used to limit the result set, but the problem is that ROWNUM is assigned when the row is 1st extracted from the database, BEFORE any ORDER BY is performed, so it can't be used in conjunction with ordering.

There is a trick involving UNION and an implicit sort that works in sme cases, but I'm not sure if you can get it to work with the GROUP BY needed here.

[Creosote]

Pexter, again reiterating my meager professional knowledge of Oracle-proprietary SQL, the solution I saw for what you describe is to subquery it:

Select * From
(Select....Order By).

No unions required there. I can't remember where I found that.

In actuality, the way I would go about this if I was using it in client program as opposed to just a report would be to put it in a stored procedure and return his two resulting column values in output parameters, instead of returning a cursor to the client for just one row.

[Pexster]

I agree, Creosote. If this value needed to be retrieved regularly, a stored procedure would definitely be the way to go!