ROW_NUMBER function page 1 

ROW_NUMBER() function numbers the rows extracted by a query. It can helps to perform more complex ordering of rows in the report, than allow the ORDER BY clause in SQL92 Standard. Before this function, to number rows extracted by a query, had to use a fairly complex algorithm intuitively incomprehensible, as described in the paragraph. The only advantage of that algorithm is that it works on almost all DBMS that support SQL92 Standard.
Notes:
Naturally, the numbering can be performed by Procedural Languages, using the cursors and/or temporary tables. But we are talking about "pure" SQL. The ROW_NUMBER function allows:
Let us show the capability of ROW_NUMBER function on the examples. Example 1 Number all flights from the Trip table in ascending order of their numbers. Order by {id_comp, trip_no}.
Solution
The OVER clause, which uses with ROW_NUMBER function, specifies the order of numbering rows. At the same time an additional ORDER BY clause is used, which has no relation to the order of output rows of the query. Considering the results it is obvious that the order of rows and the numbering, in the result set, are not coincide:
Notes:
Criteria id_comp < 3 is used only to reduce the size of a sample. Of course, the numerical order can be changed, by rewriting the last line like here:
Or, conversely, to number rows in the order given by sort:
What about renumbering of flights for each company separately? For this purpose we need to use PARTITION BY in the OVER clause. PARTITION BY specifies a group of rows for which the independent numbering is performed. Group is defined by the equality of values in the column list specified in that construction. Example 2 Renumber flights of each company separately in order of increasing numbers of flights.
PARTITION BY id_comp means that flights of each company form a group, for which an independent numbering is performed. As a result, we obtain:
Lack of PARTITION BY clause, as it was in the first example, means that all rows in the result set form one single group.
