How to implement a query similar to the TOP syntax in SQL Server, such as the top 10 query results set, etc.
Using subquery and use Oracle's function Rownum, for example:
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
