A small problem encountered in oracle paging Query

A small problem encountered in oracle paging Query

Order table, which has the same order id as the order information table (Multiple order information columns)

Find all orders and their information, and page them by order

select * from(select a. * , (DENSE_RANK() OVER(ORDER BY id DESC)) AS numindex from(SELECT o. * , DENSE_RANK() OVER(ORDER BY o.id DESC) AS rn from order o) a where rn <= 10) where numindex > 0

Find all orders and their information, and page them by order information

SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM order o  left join orderdetail od on o.id=od.orderid ) A WHERE ROWNUM <= 10 ) WHERE RN >= 0

Rank () over, dense_rank (), row_number ()

Rank () over is used to locate a specified condition and perform a ranking, but it has a feature. If it is a ranking of students, then this function is used. The two with the same scores are tied, for example, 1 2 2 4.

The role of dense_rank () is very similar to rank (). The only difference is that after the rankers' scores are tied together, the next student does not leave a parallel ranking, for example, 1 2 2 3.

Row_number () is different. The difference between row_number () and the preceding two types is obvious. This function does not need to be considered in parallel, continuous ranking is performed even if the value obtained by the condition is the same, as shown in figure

Detailed usage of rank () over, dense_rank (), row_number () http://www.cnblogs.com/wuyisky/archive/2010/02/24/oracle_rank.html