LIMIT Clause for Oracle SQL

eb110 · #1 (**permalink**) 03-17-06, 22:22

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.

andrewst · #2 (**permalink**) 03-18-06, 06:50

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech