Differences in use between RowNum and Row_number () in ORACLE (you can specify the first data for a SQL result set)

This article mainly introduces the use of RowNum and Row_number () in Oracle, as well as the differences and links, very detailed, the need for small partners can refer to.

Row_number () over (partition by col1 ORDER by col2) indicates that, according to col1 grouping, sorting is based on col2 within the grouping, and the value computed by this function represents the sequential number of each set of internally ordered (contiguous unique within the group). The difference with rownum is that when sorting with rownum, the result set is first added to a fake rownum and then sorted, and Row_number () is sorted before the row number is counted after the sort clause is included.

First, Oracle in RowNum

The number of the row returned from the query, the first row returned is assigned 1, the second row is 2, and so on, this pseudo field can be used to limit the total number of rows returned by the query, and rownum cannot be prefixed with the name of any table.

1. RowNum for query conditions equal to a value

If you want to find information about the first student in the student table, you can use Rownum=1 as a condition. But to find the information of the second student in the Student's table, the data was not found using rownum=2 results. Because rownum are all starting from 1, but more than 1 of the natural number in rownum do equal to the judgment is considered false condition, so can not find rownum = N (n>1 natural number).
Sql> Select Rownum,id,name from student where rownum=1; --There are records
Sql> Select Rownum,id,name from student where rownum = 2; --No record

2. RowNum for query conditions greater than a value

If you want to find records from the second row, you can use the following subquery method to resolve the record when using ROWNUM>2. Note that the rownum in a subquery must have an alias, or the record will not be detected, because rownum is not a column of a table, and if you cannot alias it, you cannot know whether rownum is a column of a subquery or a column of a primary query.
Sql>select * FROM (select RowNum No, id,name from student) where no>2; --There are records
Sql> SELECT * FROM (select Rownum,id,name from student) where rownum>2; --No record

3. RowNum for query conditions less than a certain value

If you want to find a third record of previous records, you can get two records when using Rownum<3. Obviously rownum for rownum<n (the natural number of n>1) is considered to be set up, so records can be found.
Sql> Select Rownum,id,name from student where RowNum <3; --There are records

4. RowNum and Sorting

The rownum in Oracle is the sequence number that is generated when fetching data, so it is important to be aware of the rowmun rows of data that you want to specify for the sorted data.
Sql> Select RowNum, id,name from student order by name;

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

As you can see, rownum is not the ordinal number generated by the Name column. The system is the number of records in the order in which records are inserted, and ROWID is assigned sequentially. To solve this problem, you must use a subquery

Sql> Select RowNum, Id,name from (SELECT * from Student order by name);

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

Ii. Oracle in Row_number ()

1, Row_number () over (order by col_1[,col_2 ...])
Follow col_1[,col_2 ...] Sort, returns the sorted result set, and returns a different value for each row.

2, Row_number () over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])
First follow the col_n[,col_m ... Group, and then follow col_1[,col_2 in each group ...] Sort (ascending), and finally returns the sorted result set

Row_number () instances in Oracle

1. Use the Row_number () function for numbering, as
Select Email,customerid, Row_number () over (order by PSD) as rows from Qt_customer
Principle: First sort by psd, after sorting, each piece of data is numbered.

2. Sort the orders in ascending order by price, and sort each record with the following code:
Select Did,customerid,totalprice,row_number () over (order by Totalprice) as rows from Op_order

3, the most recent orders for each customer is the order of the first several times.
With tabs as
(
Select Row_number () over (partition by CustomerID ORDER by Totalprice) as Rows,customerid,totalprice, do from Op_order
)
Select MAX (rows) as ' Number of orders ', CustomerID from tabs group by CustomerID

4. When using over and other window functions, the execution of the grouping and sequencing in over is later than "Where,group By,order by".
Select
Row_number () over (partition by CustomerID ORDER by INSDT) as rows,
Customerid,totalprice, did
From Op_order where insdt> ' 2011-07-22 '
The above code executes the WHERE clause first, and then each record is numbered.

Three, Row_number () and the difference between the rownum

Sorting with rownum is done by adding a fake rownum to the result set and then sorting, and row_number () sorts and then calculates the line number after the sort clause is included.

Differences in use between RowNum and Row_number () in ORACLE (you can specify the first data for a SQL result set)