LIMIT Clause for Oracle SQL

in Oracle Database; LIMIT Clause for Oracle SQL ? Hi i am quite used to php programming in MySQL. But the latest project ...
+ Post Reply + Post New Topic
Results 1 to 8 of 8
  1. #1
    amy85 is offline Junior Member
    Points: 390, Level: 7
    Join Date
    29 Jan 2009
    Posts
    11
    Points
    390

    LIMIT Clause for Oracle SQL

    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 :
    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 :
    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 :
    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. #2
    tyro's Avatar
    tyro is offline Forum Genius
    Points: 5,680, Level: 48
    Join Date
    20 Aug 2008
    Location
    India
    Posts
    368
    Points
    5,680

    Re: LIMIT Clause for Oracle SQL

    you have to use rownum subqueries because ROWNUM is applied after the results are selected, so it always starts from 1.
    Code :
    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. #3
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: LIMIT Clause for Oracle SQL

    Well , there is another way of doing it.
    By using Analytical function (Ie, Oracle 8i and above)

    Code :
    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>

    Raj.

  4. #4
    amy85 is offline Junior Member
    Points: 390, Level: 7
    Join Date
    29 Jan 2009
    Posts
    11
    Points
    390

    Re: LIMIT Clause for Oracle SQL

    hi tyro and rajavu, thanks it's working now

  5. #5
    etechpulse is offline Junior Member
    Points: 10, Level: 1
    Join Date
    25 Jul 2013
    Posts
    1
    Points
    10

    Re: LIMIT Clause for Oracle SQL

    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. #6
    DTSIGuy is offline Senior Member
    Points: 3,880, Level: 39
    Join Date
    14 Dec 2012
    Location
    Texas
    Posts
    284
    Points
    3,880

    Re: LIMIT Clause for Oracle SQL

    Why not just use the SQL*Plus command : set pause on ??

  7. #7
    zargon's Avatar
    zargon is offline Forum Guru
    Points: 26,400, Level: 97
    Join Date
    22 Oct 2008
    Location
    Aurora, CO
    Posts
    2,025
    Points
    26,400

    Re: LIMIT Clause for Oracle SQL

    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.
    David Fitzjarrell

  8. #8
    rajavu's Avatar
    rajavu is offline Forum Guru
    Points: 10,830, Level: 68
    Join Date
    13 Oct 2008
    Location
    @ Bangalore , India
    Posts
    815
    Points
    10,830

    Re: LIMIT Clause for Oracle SQL

    And for your Information, Oracle is going to have LIMIT version of MYSQL (and some other DBs ) in the for of OFFSET .. ROWS clause form the next version (Oracle 12c)
    Last edited by rajavu; 07-31-2013 at 06:07 AM.

    Raj.

Other Solutions
  1. to_char in where clause?
    By malli_18arjun in forum SQL PL/SQL
    Replies: 3
    Last Post: 10-01-2010, 04:25 PM
  2. oracle applications limit number of users
    By ewhizzard in forum Oracle Financials
    Replies: 1
    Last Post: 02-12-2010, 10:12 AM
  3. Replies: 3
    Last Post: 10-02-2009, 07:48 AM