1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

LIMIT Clause for Oracle SQL

Discussion in 'SQL PL/SQL' started by amy85, Mar 22, 2009.

  1. amy85

    amy85 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    LIMIT Clause for Oracle SQL?

    Hi i am quite used to php programming in MySQL. But the latest project i have been assigned to uses oracle in the backend. To cut a long story short, how do I page through the results of a query with oracle?

    If it was MySQL, I would simply do
    Code (Text):

    SELECT f_name, l_name, age from
    employee_data ORDER BY age DESC
    LIMIT 20
     
    This would give me only the first 20 results on first page. Now in page two of my php page I would use
    Code (Text):

    SELECT f_name, l_name, age from
    employee_data ORDER BY age DESC
    LIMIT 21,20
     
    This would give me results from row 21 to next 20 rows on second page. So what is the equivalent query for oracle? I tried with rownum.
    Code (Text):

    SELECT f_name, l_name, age from
    employee_data where rownum between 21 and 40
    ORDER BY age DESC
     
    But the above does not return any results? can someone please help...

    thanks
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    you have to use rownum subqueries because ROWNUM is applied after the results are selected, so it always starts from 1.
    Code (Text):

    SELECT f_name, l_name, age
    FROM
    ( SELECT f_name, l_name, age, ROWNUM r
      FROM
      ( SELECT f_name, l_name, age FROM employee_data
           ORDER BY age DESC
      )
      WHERE ROWNUM <= 40
    )
    WHERE r >= 21;
    This will give you the 21st to 40th rows.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Well , there is another way of doing it.
    By using Analytical function (Ie, Oracle 8i and above)

    Code (Text):

    SQL> SELECT EMPNO, ENAME, SAL  FROM EMP;

         EMPNO ENAME             SAL
    ---------- ---------- ----------
          7369 SMITH             800
          7499 ALLEN            1600
          7521 WARD             1250
          7566 JONES            2975
          7654 MARTIN           1250
          7698 BLAKE            2850
          7782 CLARK            2450
          7788 SCOTT            3000
          7839 KING             5000
          7844 TURNER           1500
          7876 ADAMS            1100
          7900 JAMES             950
          7902 FORD             3000
          7934 MILLER           1300

    14 rows selected.

    SQL> SELECT * FROM (
      2          SELECT EMPNO, ENAME, SAL ,
      3                 RANK() OVER (ORDER BY SAL DESC , ROWNUM ASC )  RN
      4          FROM EMP )
      5  WHERE RN <11;

         EMPNO ENAME             SAL         RN
    ---------- ---------- ---------- ----------
          7839 KING             5000          1
          7788 SCOTT            3000          2
          7902 FORD             3000          3
          7566 JONES            2975          4
          7698 BLAKE            2850          5
          7782 CLARK            2450          6
          7499 ALLEN            1600          7
          7844 TURNER           1500          8
          7934 MILLER           1300          9
          7521 WARD             1250         10

    10 rows selected.

    SQL>  SELECT * FROM (
      2          SELECT EMPNO, ENAME, SAL ,
      3                  RANK() OVER (ORDER BY SAL DESC , ROWNUM ASC )  RN
      4          FROM EMP )
      5  WHERE RN >=11;

         EMPNO ENAME             SAL         RN
    ---------- ---------- ---------- ----------
          7654 MARTIN           1250         11
          7876 ADAMS            1100         12
          7900 JAMES             950         13
          7369 SMITH             800         14

    SQL>
     
     
  4. amy85

    amy85 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    hi tyro and rajavu, thanks it's working now :)
     
  5. etechpulse

    etechpulse Guest

    Example 1: Returning the first 100 rows from a table
    select * from employee where rownum <= 100

    Example 2: Returning the first 100 rows in order of salary from a table
    select * from (select * from employee order by salary desc) where rownum <= 100

    Note that the rownum query has to be wrapped around an inner select that actually does the order by.

    for more visit etechpulse
     
  6. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    375
    Likes Received:
    54
    Trophy Points:
    410
    Location:
    Texas
    Why not just use the SQL*Plus command : set pause on ??
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,142
    Likes Received:
    308
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That would work but you'd also need to set the pagesize to the number of rows you want to see at one time and possibly the linesize to something greater than 80. Also using pause the first output doesn't actually display until the return/enter key is pressed:

    Code (SQL):

    SQL> SET pagesize 1 linesize 132
    SQL> SET pause ON
    SQL> SELECT * FROM emp
      2  /

          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
    ...
     
    In this case each row will be displayed after pressing the enter/return key followed by a blank line. If we set the pagesize to something more reasonable, say 7, we get:

    Code (SQL):

    SQL> SET pagesize 7 linesize 132
    SQL> SET pause ON
    SQL> SELECT * FROM emp;

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
          7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
          7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
     
    14 ROWS selected.
     
    SQL>
     
    The pagesize includes the header information so for each press of the enter/return key a new header is displayed, which may not be what the original poster wants. Also this works only for SQL*Plus; web apps running SQL statements won't be able to use the pause functionality.

    Tom Kyte, a long while back, wrote this to paginate output:

    Code (SQL):

    CREATE OR REPLACE package GetNextSet AS
       TYPE tName IS
       TABLE OF Emp.Ename%TYPE
       INDEX BY binary_integer;
       TYPE tSal IS
       TABLE OF Emp.Sal%TYPE
       INDEX BY binary_integer;
       PROCEDURE CloseCursor;
       PROCEDURE OpenCursor (pSalary pls_integer := NULL);
       PROCEDURE RetrieveRows (pRows   pls_integer := NULL,
                               pSalary pls_integer := NULL);
       PROCEDURE SetRows (pRows pls_integer);
       PROCEDURE SetSalary(pSalary pls_integer);
    END GetNextSet;
    /
     
    CREATE OR REPLACE package body GetNextSet AS
       
       vName tName;
       vSal  tSal;
       
       vRowsDefault   pls_integer := 3;
       vSalaryDefault pls_integer := 750;
       vRows          pls_integer;
       vSalary        pls_integer;
               
       cursor csrEmployees (pSalary pls_integer) IS
       SELECT Ename,
              Sal
       FROM Emp
       WHERE sal >= pSalary;
         
       /* Display retrieved rows */
       PROCEDURE ShowBatch
       IS
       BEGIN
       
          IF (vName.COUNT > 0)
          THEN
         
             FOR nIndex IN 1..vName.COUNT
             loop
               
                dbms_output.put_line(rpad(vName(nIndex), 20) ||
                                     lpad(to_char(vSal(nIndex)), 5));
               
             END loop;
                 
          END IF;
       
       END ShowBatch;
       
       /* Reset variables and collections */
       PROCEDURE Reset
       IS
       BEGIN
       
          vName.DELETE;
          vSal.DELETE;
             
          vRows   := vRowsDefault;
          vSalary := vSalaryDefault;
       END Reset;
       
       /* Close cursor */
       PROCEDURE CloseCursor
       IS
       BEGIN
         
          IF csrEmployees%isopen
          THEN
             close csrEmployees;
             
             Reset;
          END IF;
         
       END CloseCursor;
       
       /* Open cursor */
       PROCEDURE OpenCursor (pSalary pls_integer := NULL)
       IS
       BEGIN
          CloseCursor;      
             
          OPEN csrEmployees(pSalary);
       END OpenCursor;
       /* Retrieve rows from the cursor in chunks of pRows.
          If the cursor is not open, open it for the salary
          value of pSalary
       */

       PROCEDURE RetrieveRows (pRows   pls_integer := NULL,
                               pSalary pls_integer := NULL)
       IS
       BEGIN
       
         IF NOT csrEmployees%isopen
         THEN
            IF (nvl(pSalary, 0) > 0)
            THEN
               vSalary := pSalary;
            ELSE
               vSalary := vSalaryDefault;
            END IF;
         
       dbms_output.put_line('Opening the cursor.');
            OpenCursor(vSalary);
       END IF;
       
       IF (nvl(pRows, 0) > 0)
            THEN
               vRows := pRows;
            ELSE
               vRows := vRowsDefault;
            END IF;
         
         fetch csrEmployees bulk collect INTO vName, vSal LIMIT vRows;
         ShowBatch;
         
         IF csrEmployees%notfound
         THEN
         
            dbms_output.put_line('All rows retrieved. Closing the cursor.');
            CloseCursor;
         
         END IF;
           
       END RetrieveRows;
         
       /* Set the default value of rows */
       PROCEDURE SetRows (pRows pls_integer)
       IS
       BEGIN
         
          IF (nvl(pRows, 0) > 0)
          THEN
             
             vRowsDefault := pRows;
             
          END IF;
         
       END SetRows;
       
       /* Set the salary default value */
       PROCEDURE SetSalary(pSalary pls_integer)
       IS
       BEGIN
       
          IF (nvl(pSalary, 0) > 0)
          THEN
             
             vSalaryDefault := pSalary;
             
          END IF;
         
       END SetSalary;
       
    BEGIN
       
       /* Initialization */
       vRows   := vRowsDefault;
       vSalary := vSalaryDefault;
       
    END GetNextSet;
    /
     
     
    It works in this way:

    Code (SQL):

    SQL> EXEC getnextset.setrows(8)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXEC getnextset.opencursor(3000)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> SET serveroutput ON SIZE 1000000
    SQL> EXEC getnextset.retrieverows
    Opening the cursor.
    SMITH                 800
    ALLEN                1600
    WARD                 1250
    JONES                2975
    MARTIN               1250
    BLAKE                2850
    CLARK                2450
    SCOTT                3000
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXEC getnextset.retrieverows
    KING                 5000
    TURNER               1500
    ADAMS                1100
    JAMES                 950
    FORD                 3000
    MILLER               1300
    ALL ROWS retrieved. Closing the cursor.
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
    The code can be modified to use any table, and can also be used to accept a table name as input. Many possibilties exist for such a package.

    This might be more of what the OP was asking about. Rajavu also supplied an excellent alternative.
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India