SQL statements that are paged and sorted in Oracle using RowNum

SQL statements that are paged and sorted in Oracle using RowNum
Previously, paging was used to use such SQL statements:

select* from

(selectt.*,rownum row_num from mytable t order byt.id) b

whereb.row_num between 1 and10

It is found that because the statement will be rownum after the execution of the ORDER BY clause, so the sorting result is not correct, and later on Google to search 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* from mytable t order byt.id desc) a

) b

whereb.row_num between 1 and10

Reference = = = = =

http://yangtingkun.itpub.net/post/468/100278

Oracle'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) A

WHEREROWNUM <= 40

)

WHERERN >= 21

The most internal query, select * FROM table_name, represents the original query statement without paging. ROWNUM <= 40 and RN >= 21 control 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 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 through the rownum <= 40来 control The maximum value, at the outermost level of the query control the minimum value. The other way is to remove the ROWNUM <= 40 statement that queries the second layer, controlling the minimum and maximum paging values at the outermost level of the query. This is the query statement as follows:

SELECT* FROM

(

SELECTA.*, ROWNUM RN

FROM(SELECT * FROMTABLE_NAME) A

)

WHERERN BETWEEN 21 AND40

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=21&id=52022&sty=1&tpg=1&age=-1

SELECT* FROM

(SELECTA.*, rownum r

FROM

-- 這裡的SQL可以改成你真正要執行的SQL

(SELECT*  FROMArticles

ORDERBYPubTime DESC

) A

--

-- 用上面的SQL得回來的集合,使用rownum去比對,這樣rownum就會從這   

個集合的第一筆資料開始往下計算,所以這邊是抓取前100筆

WHERErownum <= 100

) B

--  B集合總共有A集合和r(rownum)的資料,這裡是抓取大於第90筆的

WHEREr > 90;

SQL statements that are paged and sorted in Oracle using RowNum