#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    India
    Posts
    20
    Rep Power
    0

    How to imit the output of a query?


    Hi friends

    Can any one help me in a query where i want to limit the output of a query. For example if a query returns 10 records i want to see only first 5 or last five..

    Or

    In other words i want the functionality similar to LIMIT clause of MySQL in oracle

    thanks
    Neeraj
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    Which version of Oracle are you using? In Oracle 9i you can do this by using sub query in FROM clause of SELECT statement, An example is givn below:

    SELECT *
    FROM (SELECT empno, ename, sal, deptno FROM emp ORDER BY empno DESC)
    WHERE rownum<6
    /
  4. #3
  5. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    above query can be used to display the last five records from the table, to display the first five record from the table use following query:

    SELECT * FROM emp
    WHERE rownum <6
    ORDER by empno
    /
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    12
    Just to be sure that we are on the same page:

    Shafique's query:

    SELECT * FROM emp
    WHERE rownum <6
    ORDER by empno
    /

    will return 5 arbitrary rows from the emp table and these 5 rows will be ordered by empno.
    Let's be very careful with rownum - It is assigned only when the query is _fetched_.
    On second thought, maybe it's not that confusing: after all the where clause is always executed before the order clause.
    Right?

    Cheers,
    Dan

IMN logo majestic logo threadwatch logo seochat tools logo