SQL statements that are paged and sorted by RowNum in Oracle are used to using such SQL statements before paging:Select * from (SelectT.*, RowNum row_num fromMyTable TOrder byt.id) bwhereB.row_numbetween 1 and Tenit turns out that because the statement will be rownum after the order is executed, byclause, so the sorting result is not right, and later on Google found an article, the original multi-layer select can be a good solution to the problem, hereby recorded, the statement is as follows:Select * from (SelectA.*, RowNum row_num from (Select * fromMyTable TOrder byT.iddesc) a) bwhereB.row_numbetween 1 and Ten====Reference====http://Yangtingkun.itpub.net/Post/468/100278Oracle's paging query statements can be applied basically in the format given in this article. Paged Query format:SELECT * from( SELECTA.*, ROWNUM RN from(SELECT * fromtable_name) AWHEREROWNUM<= +)WHERERn>= +one of the most inner queries select* fromTABLE_NAME represents the original query statement without paging. ROWNUM<=40 and RN>=21 controls the range of pages per page for paged queries. The paging query statement given above has high efficiency in most cases. The purpose of paging is to control the output result set size and return the results as soon as possible. In the above paged query statement, this consideration is mainly reflected in the where ROWNUM<=40 on this sentence. There are two ways to select the 21st to 40th record, one of which is shown in the above example in the second layer of the query via RowNum<=The 40来 controls the maximum value, controlling the minimum value at the outermost of the query. And the other way is to get rid of the second layer of where ROWNUM<=40 statement, which controls the minimum and maximum paging values at the outermost of the query. This is the query statement as follows:SELECT * from( SELECTA.*, ROWNUM RN from(SELECT * fromtable_name) A)WHERERnbetween + and +In contrast to these two formulations, the first query is much more efficient than the second in most cases. http://www.javaworld.com.tw/Jute/Post/View? bid= +&Id=52022&Sty=1&Tpg=1&Age=-1SELECT * from (SELECTA.*, RowNum R from --the SQL here can be changed to the SQL you really want to execute.(SELECT * fromArticlesORDER byPubtimeDESC) A-- --Using the above SQL to get back the collection, use RowNum to compare, this rownum will be from thisthe first material in the collection starts down, so this is the top 100 strokesWHERERowNum<= -) B--The B collection always has a collection of A and R (RowNum), and this is the crawl of the 90th pen. WHERER> -;
SQL statements that are paged and sorted in Oracle using RowNum