Re: How to define an alias for a subquery?

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

    SELECT temp.id_hotel
    FROM (

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

In other words, don't use AS.

Hth,
Radu
"mario" <mario_34_at_hotmail.com> wrote in message news:ab0nqb$9r8$1_at_planja.arnes.si...
> Hi,
>
> does anybody know how to define alias for a subquery ? Maybe it's better
if
> i show an example:
>
> The query below should retrieve the names of hotel(s) which have the
maximum
> number of rows in table
> "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
> following WHERE clause.
>
> Any suggestions on how to define the alias "temp"?
>
> Thanx
>
>
>
Received on Sat May 04 2002 - 19:55:24 CEST