SQL statements that are paged and sorted in Oracle using RowNum

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.