New sql features

Ranking functions

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.

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_comp
FROM trip
WHERE ID_comp < 3
ORDER BY id_comp, trip_no;

SELECT row_number() over(order by trip_no) num, trip_no, id_comp FROM trip WHERE ID_comp < 3 ORDER BY id_comp, trip_no;

    Scheme

No scheme

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:

Of course, the numerical order can be changed, by rewriting the last line like here:

ORDER BY trip_no

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 trip
WHERE ID_comp<3
ORDER BY id_comp, trip_no;

SELECT row_number() over(order by id_comp, trip_no) num, trip_no, id_comp FROM trip WHERE ID_comp<3 ORDER BY id_comp, trip_no;

    Scheme

No scheme

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 trip
WHERE ID_comp < 3
ORDER BY id_comp, trip_no;

SELECT row_number() over(partition by id_comp order by id_comp,trip_no) num, trip_no, id_comp FROM trip WHERE ID_comp < 3 ORDER BY id_comp, trip_no;

    Scheme

No scheme

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.