Limiting result set in Oracle query

[Anon]

Greetings,

Is there a way to retrive fixed numer of rows from a oracle using oci extensions. I am looking for something similar to LIMIT 1,50 claulse in mysql query. Please help.

Thanks

[Anon]

Hello,

You could use rownum to do this. Something like:

SELECT FIELD1,FIELD2
FROM TABLE
WHERE ROWNUM BETWEEN 1 AND 50

This will retrieve only first 50 records from database.

Altought rownum does not work fine with ORDER BY clauses because rownum is assigned by Oracle after the records are retrieve and before they are returned to you. If you are using a newest version of Oracle (8i) you can remedy this situation with a sub-select.

Best Regards

[Anon]

Hi,

If one want to display next set of records it will not work

SELECT FIELD1,FIELD2
FROM TABLE
WHERE ROWNUM BETWEEN 51 AND 100

Idea is to split a large query result in to pages with next and privious links.

Thanks

[Anon]

You'll need to refetch 50 rows then fetch the records you need.

eg:
1st page: rows 1-50

SELECT xxx
FROM yyy
WHERE ROWNUM < 51

2nd page: rows 51-100
SELECT xxx
FROM yyy
WHERE rownum < 101

Fetch rows 1 to 50 without outputing them then fetch and display rows 51 to 100.

it's a raw method but the only way with Oracle.
You'll need to reparse and refetch your cursor every time.

Hth

JBL