Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: Rownum query with joins ?

    I need a query like this :

    select * from (select *from tab1 where rownum < 4) a inner join
    (select *from tab2 where rownum < 4) b on a.sno = b.frsno

    that is first 4 rows of both the table to be fetched and then over that inner join to be performed.

    What is the query for this in Oracle ? The above throws syntax error .

    Please advise.

    Thanks,
    Sam

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It would help if you told us what syntax error you get. I suspect you are using 8i and the ANSI "inner join" syntax is not supported.

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    No I am using only 9i, it says that, I can't use sub query there instead of tables in JOIN condition.

    Is there any alternative query to below concept :

    First choose the first 10 rows of two tables.
    And then perform the inner join over the above result set.

    Please advise.

    Thanks,
    Sam

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Presumably you have hit a 9i restriction - I just checked on 10G and it works there.

    You can rewrite using non-ANSI syntax like this:

    select * from (select *from tab1 where rownum < 4) a,
    (select *from tab2 where rownum < 4) b
    where a.sno = b.frsno

    That works in 8i, so should be fine in 9i.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •