Use rownum in Oracle

In Oracle, rownum is the sequence number generated when data is retrieved. Therefore, you must pay attention to the specified rowmun rows of data to be sorted.

 

SQL> select rownum, id, name from student order by name;

 

ROWNUM ID NAME

 

3 200003 Li San

 

2 200002 Wang 'er

 

1 200001 Zhang Yi

 

4 200004 Zhao Si

 

It can be seen that rownum is not the serial number generated by the name column. The system assigns the number of the record row according to the sequence in which the record is inserted, and the rowid is also allocated sequentially. To solve this problem, you must use the subquery

 

SQL> select rownum, id, name from (select * from student order by name );

 

ROWNUM ID NAME

 

1 200003 Li San

 

2 200002 Wang 'er

 

3 200001 Zhang Yi

 

4 200004 Zhao Si