ORACLE ROWNUM函数（使用分页页面）

Question

I still confused with the ROWNUM oracle function.

Last time I using LIMIT in MySQL function worked like this :

$page -= 1;
$per_page = 3;
$previous_btn = true;
$next_btn = true;
$first_btn = true;
$last_btn = true;
$start = $page * $per_page;

$sql = ... WHERE ... LIMIT $start, $per_page;

But now how can I use ROWNUM in ORACLE ?

$sql = ... WHERE ... ROWNUM $start, $per_page;

I using this because I have the pagination page.

Anyone please advice.

Answer 1

In Oracle, Rownum values are assigned only after the filtering stage of the query, So you must put it as a inline view. like,

select *
  from (select col1, col2, ROW_NUMBER() OVER(ORDER BY col1 ASC) RowNumber 
       FROM table)
 where rownumber between 1 and 10

Or

select *
  from (select col1, col2, ROW_NUMBER() OVER(ORDER BY col1 ASC) RowNumber 
       FROM TABLE)
 WHERE RowNumber > 1 AND RowNumber < 10;