Precedence comparison of rownum and order by in Oracle

1. Preface

　　The company recently MySQL database switch oracle,oracle for a long time useless a little strange, just started to do the paging on the rownum and order by priority comparison. Check the information, the Internet said rownum priority is higher than the order by, that is, the results of the first fetch data, and then the results are sorted. With a skeptical attitude to write a two-day SQL statement, the result is ROWNUM priority is higher than the orderby, some of the order by priority is higher than ROWNUM. It was later concluded that the priority comparison of RowNum and order by was to be divided.

2. The subject

2, 1 order by index, primary key, Span style= "FONT-SIZE:18PT;" >

Select  from where <= Ten Order  by desc ;
Select  from (Select from Test  Order  by desc where rownum<=;

2, 2 order by ordinary column, where amount is the normal column, the execution results are as follows, the results are not the same. 　

1、SelectAmount fromTestwhereRowNum<=Ten Order  byAmountdesc;
2、SelectB.amount from(SelectA.amount fromTest AOrder  byA.amountdesc) bwhereRowNum<=Ten;

　　

3. Results

　　The priority comparison of the rownum and order by is a case in point,when the order by index, the primary key, allows Oracle to sort by the field first, then the RowNum label, and the Order by normal column, rownum the after order by.

Precedence comparison of rownum and order by in Oracle