Finding the MAX

Question

Possible Duplicate:
Oracle SQL - How to Retrieve highest 5 values of a column

I have a simple question for someone who knows anything about SQL but since i'm very new and although I have tried many different ways, I can never seem to get the syntax correct. I want to display only the average hotel which is the MAX result. Currently it displays the average of all the hotels individually. Is this something I can use the MAX function for? I am using oracle

SELECT HOTEL.H_NAME "HOTEL NAME", ROUND(AVG (R_PRICE), 1) "AVERAGE ROOM PRICE"
FROM ROOM JOIN HOTEL ON HOTEL.HOTEL_NO = ROOM.HOTEL_NO
WHERE HOTEL.H_NAME = 'Shangra_La'
OR HOTEL.H_NAME = 'Hilton'
OR HOTEL.H_NAME = 'Sheraton'
GROUP BY HOTEL.H_NAME
ORDER BY HOTEL.H_NAME;

The MAX function will give you the highest value, that is the highest price. I don't understand what you mean by "the average hotel which is the MAX result". — Marlin Pierce, Oct 18 '12 at 11:23
Please could you elaborate on what `I want to display only the average hotel which is the MAX result.` means? If possible, include example input data and an example fo the output you desire/expect. — MatBailie, Oct 18 '12 at 11:30
Sorry guys I worded it poorly. Currently the query displays the average room price of 3 separate hotels. I want the query to return the Hotel which has the highest average price per hotel room out of the 3. — dan boy, Oct 18 '12 at 11:31
@danboy - The terms highest and average are usually mutually exclusive. Say three hotels `A, B, C` have prices of `150.00, 169.00, 180.00`. The *average* price is ~`166.33`. None of them have the "average" price. However hotel `C` has the highest price: `180.00`. So what do you mean by "highest average price". As mentioned, a data sample would really help clarify things. — Leigh, Oct 19 '12 at 05:09

score 0 · Accepted Answer · answered Oct 18 '12 at 11:32

0

In Oracle you can use the ROWNUM pseudo column.

SELECT * FROM
(
    SELECT 
        HOTEL.H_NAME "HOTEL NAME", 
        ROUND(AVG (R_PRICE), 1) "AVERAGE ROOM PRICE" 
    FROM
        ROOM 
            JOIN HOTEL ON HOTEL.HOTEL_NO = ROOM.HOTEL_NO 
    WHERE HOTEL.H_NAME IN ('Shangra_La','Hilton','Sheraton')
    GROUP BY HOTEL.H_NAME 
) v
WHERE ROWNUM = 1
ORDER BY "AVERAGE ROOM PRICE" DESC;

answered Oct 18 '12 at 11:32

podiluska

50,950
7
98
104

`Order by` clause should be applied to the sub-query if you want to get maximum value of price. Otherwise you are just ordering 1 row - first. – Nick Krasnov Oct 18 '12 at 11:48

Finding the MAX

1 Answers1