1

Possible Duplicate:
How do I limit the number of rows returned by an Oracle query after ordering?

I am using just like below query:

SELECT url, title, metadesc FROM (
        SELECT url,title, metadesc, rownum rn FROM (
                SELECT url, title, metadesc FROM web
            )
            WHERE rownum <= 60
    )
    WHERE rn > 50

it works fine for single table but if do join in my main query i.e 

SELECT name, id, url, title, metadesc
FROM web, emp

then it gives me error:

java.sql.SQLException: ORA-00918: column ambiguously defined
Community
  • 1
  • 1
Pravin
  • 1,047
  • 11
  • 20
  • 2
    See this: http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering?rq=1 – esmoreno Sep 03 '12 at 09:54
  • Starting from Oracle 12cR1, you can use `OFFSET` and `FETCH NEXT n ROWS`: http://stackoverflow.com/a/26051830/1461424 – sampathsris Sep 26 '14 at 10:48

1 Answers1

1

Use either aliases:

SELECT e.name, e.id, w.url, w.title, w.metadesc
  FROM web w
  JOIN emp e ON w.xx = e.yy

Or full table names (more verbose):

SELECT emp .name, emp .id, web.url, web.title, web.metadesc
  FROM web
  JOIN emp ON web.xx = emp .yy

If a subquery returns two columns with the same name, you can't do a SELECT *:

SQL> SELECT * FROM (
  2     SELECT d1.dummy, d2.dummy
  3       FROM dual d1, dual d2
  4  );

ERROR at line 1:
ORA-00918: column ambiguously defined

In that case use different column names in the subquery with aliases:

SQL> SELECT * FROM (
  2     SELECT d1.dummy dummy_d1, d2.dummy dummy_d2
  3       FROM dual d1, dual d2
  4  );

D D
- -
X X
Vincent Malgrat
  • 63,744
  • 9
  • 111
  • 163
  • thanks a lot Vincent Malgrat its works but its take much time is there any other ways to implement pagination in oracle ????? – Pravin Sep 03 '12 at 11:19
  • This would be an optimization question. I don't have enough data to answer. I suggest you create a new question with a description of your tables and their data and your query with its optimizer plan. – Vincent Malgrat Sep 03 '12 at 12:42