MySQL Oracle Paging

(1) The limit m,n statement for MySQL

In the two parameters after limit, the parameter m is the starting subscript, which starts at 0 and the parameter n is the number of records returned. We need to specify these two values when paging.

For example: Query 10 rows of records, starting from 3.

SELECT * from EMP LIMIT 3, 10;

(2)rownum of Oracle database

the subquery is to be enclosed within parentheses.

Place the subquery on the right side of the comparison condition.

single-row operators correspond to single-line subqueries, and multiline operators correspond to multiple rows of subqueries.

subqueries can appear in the select,from,where,having clause

subqueries may not appear in the GROUP BY clause

the main query and subquery can use or not use a single representation

subqueries that follow the FROM clause are most important (for example, Oracle paging statements)

Select Yy.*
From (select RowNum ids,emp.* from emp where rownum<=9) yy
where ids>=5;

Example: If we want to sort the column Sal in the EMP table and then page it, we can write that.

SELECT *
From (select RowNum r,e1.*
From (SELECT * from emp ORDER by Sal) E1
where RowNum <=8
)
where R >=5;

MySQL Oracle Paging