SQL Inner join on select statements

Question

Hello, I am trying to make an inner join on a select statement like this:

select *
from (select* from bars  where rownum <= 10 )as tab1
inner join (select * from bars  where rownum <= 10 )as tab2
on tab1.close=tab2.close

and I get the following error: ORA-00933 SQL command not properly ended Any help would be appreciated, thank you!

Answer 1

Just remove as from your query:

select *
from (select* from bars  where rownum <= 10 ) tab1
inner join (select * from bars  where rownum <= 10 ) tab2
on tab1.close=tab2.close

Answer 2

select * from 
((select* from bars  where rownum <= 10 )as tab1
inner join (select * from bars  where rownum <= 10 )as tab2
on tab1.close=tab2.close)

Answer 3

I believe the error comes from you needing a semicolon to end the statement. The select looks fine to me otherwise.

Answer 4

Not related to your error message, but I think you need an ORDER BY in there somewhere, as otherwise there's no guarantee as to which top 10 rows you're going to get:

...
select* from bars  order by <some col> where rownum <= 10
...

See here for more info:

http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html