Home » Oracle » Oracle query before 10 records and query (fifth to...

Oracle query before 10 records and query (fifth to tenth records)


If you want to know Oracle query of the first 10 records related to application programs, you can click the following article on the correct usage in the actual operation of a more comprehensive understanding, in the hope that you browse the details of the following articles will the following is the text of the introduction of.



How do you query the top10 record in the table Oracle?




select*
Fromtest
Whererownum<=10




Here's an introduction to rownum,



The use of Rownum and row_number () over ()



ROWNUM is a pseudo column provided by Oracle from 8, numbering the results from SQL and always starting from 1. The common use is to output.

by paging


Such as




SELECT*
FROMtorderdetaila
WHEREROWNUM<=10




This statement is the output of the first 10 records, here in use similar to the SQL Sever top, but rownum for the specified number interval output should be more powerful,




SELECT*
FROM (SELECTa.*, ROWNUMrn)
FROMtorderdetaila)
WHERErn>=10ANDrn<=20




This statement is the output of the Oracle query tenth to twentieth records, the rownum RN, is to turn into a rownum instance, because rownum itself can only use the comparison only to < =, is listed, so do the comparison of > =.



In practical applications, often require several records recently, it needs to sort the record again after rownum <=



Common




SELECT*
FROM (SELECTa.*
FROMtorderdetaila
ORDERBYorder_dateDESC)
WHEREROWNUM<=10




In the case of CSDN, there was a discussion about the near 10 record, and someone gave the statement,




SELECTa.*
FROMtorderdetaila
WHEREROWNUM<=10
ORDERBYorder_dateDESC




The reason why there will be such a statement, mainly from efficiency considerations before the statement is to make noodles, a full table scan after sorting, and then take 10 records, after a statement is not a full table scan will only remove 10 records, apparently after the statement will be many high efficiency.



Why is the dispute, controversy in the execution of the order, is the first implementation of order 10 records, or 10 records, and then sort of? Two results in order to take out the opposite, Oracle query before 10 records before ordering another 10, is to take the nearest 10, the first 10, and then sort, remove the first 10 records. For this statement, generally considered the order of execution is to take 10 records to sort.



So this statement should be wrong. But this is not the case, there is relationship between the fields of this statement execution order and order by, if you order by field is PK, is the first sort, then 10 (faster than the first statement, and fast) sort field is not PK, is the first of 10 re ranking at this time, and the result is the requirement is not the same, so the second one must be in the field is the primary key to sort the case to ensure correct result.



Row_number () over () the analysis function is provided from 9I, and the general uses are almost rownum and.



General row_number over (order) method (by order_date DESC) sequence and generated the rownum statement, as well as efficiency (for the rownum statement has the same order of the by), so in this case the two usage is the same as.



And for the group after 10 records recently, rownum is unable to realize, only row_number can be achieved, row_number (over) (partition by order by group field sort field) can be achieved after grouping the number, for example to take nearly a month at the end of the day 10 order record




SELECT*
FROM (SELECTa.*,
ROW_NUMBER () OVER (PARTITIONBYTRUNC (order_date) ORDERBYorder_dateDESC)
RN
FROMtorderdetaila)
WHERErn<=10




Offbeat usage of Rownum, sometimes we will meet this demand, output all the days of the month, many people will worry, and there is no such table in the database, how to output all the number of days a month? Rownum:

can solve



SELECTTRUNC (SYSDATE,'MM') +ROWNUM-1
FROMDUAL
CONNECTBYROWNUM<=TO_NUMBER
(TO_CHAR (LAST_DAY (SYSDATE),'dd'))




The above is the introduction to the first 10 records of the Oracle query.





To sum up: Oracle query fifth to tenth records paging query:



select * from (select, a.*, rownum, RN, from (select *, from, scott.emp), a, where, rownum<=10), where, rn>=5,

Latest