The relationship between ROWNUM and ORDER BY in Oracle

xiaoxiao2021-04-05  269

When you just learn Oracle, you will be the main thing about ROWNUM is the same mechanism similar to the SQL Server Top keyword ---- At least the most common paging of our most commonly used this. This is not the case, when learning, "empty" is very important, and the previous experience is often a bad place. In SQLServer, if the Top keyword and the Order By keyword appear together, it is first Order By and then TOP. That is to say: A few records acquired are sorted. In Oracle, if you use WHERE ROWNUM <= N and Order By, Rownum takes precedence over the order by. That is, the ORDER BY cannot sort all the records satisfied, only N records in the current ROWNUM <= N are sorted. Look at this page of this pagination: SELECT LIMITRESULT. * From (SELECT *, ROWNUM AS R from ATABLE WHERE ROWNUM <= 40 ORDER BY CREATETIME DESC) LIMITRESULTRESULTRESULTETIME LIMITRESULTRESULTRESULTWHERE LIMITRESULT.R> 20-- Take the second page query As a result, only the CreateTime of the current page is arranged in reverse, and does not arrange in the reverse order of all records. Note ROWNUM is prioruous than ORDER BY. The query modification forced into the following way:. (. SELECT OrderResult *, rownum AS r FROM (SELECT * FROM ATable ORDER BY createTime DESC) OrderResult WHERE rownum <= 40) LimitResultWHERE LimitResult.r> SELECT LimitResult * FROM 20 efficiency is not significant I used to be high, but there is no way. The above is the law I found, I hope that friends can make better ways. Thank you!

转载请注明原文地址:https://www.9cbs.com/read-132037.html

New Post(0)