How to implement a paging query similar to the Top syntax in the Oracle, such as the top 10 of the query results set, the 10th to 20th query results set?
The answer is the use of subqueries and uses Oracle's function ROWNUM, as follows:
There is a user table as follows:
User
Userid Number (10) Not null,
Status Number (3),
Email varchar2 (256) Not null,
Username varchar2 (256),
Password varchar2 (256),
ConsTRAINT PK_USER_1 Primary Key (UserID)
)
The first 10 users sorted by the query status is 1, SQL is as follows:
Select * from (select a. *, rownum rn from user a where a.status = 1 Order by a.username)
WHERE RN <= 10
The practice is to put our query in a subquery, and query a result, it is the number of rows, use Rownum
Then do the number of where conditions in the outside query, query different result sets as needed, if the top 10 is RN <= 10, if it is 10 ~ 20, is RN <20 and Rn> = 10, etc.
Performing the above SQL query will find that the 10 results taken out are not necessarily the top 10 after ordering, because the Oracle's ROWNUM function actually generates a pseudo column, its line number is not completely in the order of the result set, A certain randomness, so you need to make some modifications to the above statements, use the Row_Number () function, as follows:
Select * from (select a. *, row_number () over (ORDER BY A.USERNAME) RN from User a where a.status = 1)
WHERE RN <= 10
