Oracle in RowNum and Row_number ()

Simple Introduction to Oracle in RowNum and Row_number () use, example demo.

Reference: http://www.cnblogs.com/zjrstar/archive/2006/08/31/491090.html

Let's start by creating an example to illustrate, stealing a lazy, using the child's shoes above.

Create TableStudent (IDChar(6), nameVARCHAR2(Ten));Insert  intoStudentValues('200001','Zhang Yi');Insert  intoStudentValues('200002','Wang er');Insert  intoStudentValues('200003','lie triple');Insert  intoStudentValues('200004','Zhao Four');Commit;

1)

Sql> SELECT * FROM student where rownum>=1;

ID NAME
------ ----------
200001 Sheets A
200002 Wang ER
200003 lie triple
200004 Zhao Si

2)

Sql> Select RowNum, t.* from student t where rownum>=1;

ROWNUM ID NAME
---------- ------ ----------
1 200001 Zhang One
2 200002 Wang ER
3 200003 Lie triple
4 200004 Zhao Si

3)

Sql> Select RowNum, t.* from student t where RowNum <3;

ROWNUM ID NAME
---------- ------ ----------
1 200001 Zhang One
2 200002 Wang ER

4)

Sql> Select RowNum, t.* from student t where RowNum >=2;

Row not selected

Sql>

Why?=>1.rownum is a pseudo-column; rownum always starts at 1 and keeps on.

5)

Sql> Select rownum,t.* from student t order by name DESC;

ROWNUM ID NAME
---------- ------ ----------
4 200004 Zhao Si
1 200001 Zhang One
2 200002 Wang ER
3 200003 Lie triple

6)

Sql> Select Row_number () over (order BY name Desc) RN, t.* from student t;

RN ID NAME
---------- ------ ----------
1 200004 Zhao Si
2 200001 Zhang One
3 200002 Wang ER
4 200003 Lie triple

Above 5) and 6) can be compared: why the results of both rownum and RN are different, note row_number () over (...) Usage.

7)

Sql> SELECT * FROM (select RowNum rn,t.* from student t) TT where TT.RN between
2 and 3;

RN ID NAME
---------- ------ ----------
2 200002 Wang ER
3 200003 Lie triple

This way can be paged data, but remember the above 5), pseudo-column rownum is not ordered, if you want to sort, try to use RowNumber () over (...)

8)

Sql> SELECT * FROM (select Row_number () over (order BY name Desc) RN, t.* from St
Udent t) TT where TT.RN between 2 and 3;

RN ID NAME
---------- ------ ----------
2 200001 Zhang One
3 200002 Wang ER

9)

Sql> SELECT * FROM (select RowNum RN, t.* from student t order by name DESC) TT W
Here Tt.rn between 2 and 3;

RN ID NAME
---------- ------ ----------
2 200002 Wang ER
3 200003 Lie triple

Please compare 8) and 9) to understand the note below 7).

Well, that's all, I think the examples above illustrate the common usage of rownum and row_number, as well as the use of their features.