select
*
from
(
select
rating,
avg(age) as avgage
from
sailors
group by
rating
) as temp
where
temp.avgage = (
select
min(temp.avgage)
from
temp
);
When i am trying to run the above command i am getting the following error
ORA-00933: SQL command not properly ended
The Sailors table looks like this Sailors Table
Could you tell why i am getting this error?
Table aliases in Oracle don't have AS (columns can, but don't have to).
Therefore:
No : ) as temp
Yes: ) temp
As of ORA-00942: temp isn't accessible in a subquery. But, if you use it (the temp) as a CTE or an inline view (so that it is a source readable by the whole query), then it should be OK. Something like this:
with temp as
(select rating, avg(age) as avgage
from sailors
group by rating
)
select *
from temp a
where a.avgage = (select min(b.avgage)
from temp b
);