How to define an alias for a subquery?

From: mario <mario_34_at_hotmail.com>
Date: Sat, 4 May 2002 15:30:27 +0200
Message-ID: <ab0nqb$9r8$1_at_planja.arnes.si>

Hi,

[Quoted] does anybody know how to define alias for a subquery ? Maybe it's better if [Quoted] i show an example:

The query below should retrieve the names of hotel(s) which have the maximum number of rows in table
[Quoted] "records". The problem is the "AS temp" clause...

---

SELECT hotel.hotel_name
FROM hotel
WHERE hotel.id_hotel IN
 (

    SELECT temp.id_hotel
    FROM (

                  SELECT recs.id_hotel, count(*) AS cnt
                  FROM  records recs
                  GROUP BY id_hotel )  AS temp
           WHERE temp.cnt = ( SELECT max(temp.cnt) FROM temp )
        );


*******************************************************

...if i execute this query i get the error message

    AS temp
    *
ERROR at line 10:
ORA-00907: missing right parenthesis

On the other hand, i have to define an alias for the subquery, since i need it in the
[Quoted] following WHERE clause.

Any suggestions on how to define the alias "temp"?

Thanx Received on Sat May 04 2002 - 15:30:27 CEST