Results 1 to 2 of 2
Thread: LIMIT Clause for Oracle SQL
03-17-06, 23:22 #1Registered User
- Join Date
- Mar 2006
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.
03-18-06, 07:50 #2Moderator.
- Join Date
- Sep 2002
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:
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;Tony Andrews