Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    LIMIT Clause for Oracle SQL

    Hello, I'm developing web app with PHP and MySQL. And some how I also have to use Oracle DB. I'm just very new to Oracle/ Oracle SQL and have a problem to limit number of data to show for search result.

    My problem is how to do search result pagenation with Oracle/Oracle SQL.
    MySQL is simply use LIMIT clause.

    I found out that I can use ROWNUM to limit search result to output.

    So, I've tried following SQL statement.
    Limit 20 per page and 25 total result for this example.

    SELECT * FROM RECORD WHERE (ROWNUM >= 1 AND ROWNUM <= 20) AND AVAIL='Y" ORDER BY DATE DESC

    Then netxt page query will be:
    SELECT * FROM RECORD WHERE (ROWNUM >= 21 AND ROWNUM <= 25) AND AVAIL='Y" ORDER BY DATE DESC

    But I don't get any result for second page.
    Please someone help.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    ROWNUM is applied after the results are selected, so it always starts from 1 and "WHERE ROWNUM >= 21" will never be true. You can achieve the effect you want like this:

    Code:
    SELECT a, b, c
    FROM
    ( SELECT a, b, c, ROWNUM rn
      FROM
      ( SELECT a, b, c FROM RECORD 
        WHERE AVAIL='Y"
        ORDER BY DATE DESC
      )
      WHERE ROWNUM <= 25
    )
    WHERE rn >= 21;

Posting Permissions

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