Use of the Oracle keyword rownum

The Oracle keyword rownum uses Rownum, which is a pseudo column provided by oracle. We use a lot of data during paging. If rownum is improperly used, it will often be crazy. Assume that the table structure is as follows: www.2cto.com tid tname ttype5 T1 Q14 T4 Q13 T3 Q72 T2 Q21 T1 Q66 T6 Q17 T7 Q38 T8 Q39 T9 Q410 T10 Q5 execution statement: SELECT rownum, t. * FROM t_test t order by t. tid; the expected results should be: www.2cto.com rownum tid tname ttype1 1 T1 Q62 2 T2 Q23 3 T3 Q74 4 T4 Q15 5 T1 Q16 6 T6 Q17 7 T7 Q38 8 T8 Q39 9 T9 Q410 T10 Q5 but the actual results are true: rownum tid tname ttype5 1 T1 Q64 2 T2 Q23 3 T3 Q72 4 T4 Q11 5 T1 Q16 6 T6 Q17 7 T7 Q38 8 T8 The results produced by Q39 9 T9 Q410 10 T10 Q5 are not sorted as expected before generating the rownum value. The generated rownum value is in the order when we add data. There are two ways to solve this problem: 1. Create a primary key constraint for the order by field. 2. Use rownum to the outer layer. Statement: select rownum, tt. * FROM (SELECT * FROM t_test t) tt adopts one of the two methods, and the output result is the same as we expected. However, another problem was found during the use process. When rownum is used for grouping words, the disordered order is displayed. SQL statement: SELECT * FROM (SELECT MAX (t. tid), MAX (t. tname), t. ttype FROM t_test t group by t. ttype) tt where rownum <3 does not display the first two rows of data as expected. There are two solutions for www.2cto.com: 1. SELECT * FROM (select max (t. tid), MAX (t. tname), t. ttype FROM t_test t group by t. ttype order by max (t. tid) tt where rownum <3 2. SELECT ttt. * FROM (select rownum row_num, tt. * FROM (select max (t. tid), MAX (t. tname), t. ttype FROM t_test t group by t. ttype) tt) ttt where rownum <3