Usage of rownum in Oracle ____oracle

1, query the records of the first few lines

Select Sal from EMP where rownum=1; Query gets the first line of records

Select Sal from EMP where rownum=5; You cannot query to line fifth records, because RowNum is always queried from 1, so it is not possible to get a record of the first few lines in this way. If you want to get the fifth line of records, you should use the following methods:

Select R,sal from (select RowNum r,sal from EMP) where r= 5;

2, used to get the first few lines of records, that is less than a record of a value.

Example: A record in the first four lines of a query

Select Rownum,sal from emp where rownum<5;

3, to obtain the records of the following lines, that is, more than a certain value records. Example: query for records after line fifth

Select R,sal from (select RowNum r,sal from emp) where r>5;

4, query a range of records. For example, check the record of the third line in eight lines:

Select R,sal from (select RowNum r,sal from EMP) where r>=3 and r<=8;

5, RowNum and sorting

Select Rownum,sal from emp order by Sal;

From the results of this statement, you can see that the rownum is not increased from 11 times, but rather chaotic, in fact, these rownum is the number of rows before each record is not sorted, of course, this is not the result we want, then how to get RowNum is also the order of the query results. This requires sorting the original records first, and then extracting the rownum and desired records from the new Order. Such as:

Select Rownum,sal from (SELECT * to emp order by Sal);

Select RowNum, sal from (SELECT * from emp order by Sal) where RowNum <5;

Select R, Sal from (select RowNum r,sal from (SELECT * to emp ORDER by Sal) where R >5;

Select R, Sal from (select RowNum r,sal from (SELECT * to emp ORDER by Sal)

where R >5 and R < 10;