17:43

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 SQL-92 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 SQL-92 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:

• specify the numbering, which differs from the order of rows in the result set;
• create a "non-through" numbering, i.e. select group of the total number of rows and number them separately for each group;
• use multiple methods of numbering, because actually the numbering does not depend on the ordering of a query strings.

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

Console
Execute
`SELECT row_number() over(ORDER BY trip_no) num,  trip_no, id_compFROM tripWHERE ID_comp < 3ORDER BY id_comp, trip_no;`

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:

num trip_no id_comp
3 1181 1
4 1182 1
5 1187 1
6 1188 1
7 1195 1
8 1196 1
1 1145 2
2 1146 2

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:

`ORDER BY trip_no`
(or, equivalently, order by num)

Or, conversely, to number rows in the order given by sort:

Console
Execute
`SELECT row_number() over(ORDER BY id_comp, trip_no) num,  trip_no, id_comp FROM tripWHERE ID_comp<3ORDER BY id_comp, trip_no;`

num trip_no id_comp
1 1181 1
2 1182 1
3 1187 1
4 1188 1
5 1195 1
6 1196 1
7 1145 2
8 1146 2

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.

Console
Execute
`SELECT row_number() over(partition BY id_comp ORDER BY id_comp,trip_no) num, trip_no, id_comp FROM tripWHERE ID_comp < 3ORDER BY id_comp, trip_no;`

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:

num trip_no id_comp
1 1181 1
2 1182 1
3 1187 1
4 1188 1
5 1195 1
6 1196 1
1 1145 2
2 1146 2

Lack of PARTITION BY clause, as it was in the first example, means that all rows in the result set form one single  group.

Suggested exercises: 65, 97, 116, 125, 130, 137

 Pages 1 2
Tags
The book was updated
several days ago
https://exchangesumo.com/obmen/RUSSTRUB-STLMRUB/