loading..
Русский    English
07:59

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
  1. SELECT row_number() over(ORDER BY trip_no) num,
  2. trip_no, id_comp
  3. FROM trip
  4. WHERE ID_comp < 3
  5. ORDER 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:

  1. ORDER BY trip_no
(or, equivalently, order by num)

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

Console
Execute
  1. SELECT row_number() over(ORDER BY id_comp, trip_no) num,
  2. trip_no, id_comp
  3. FROM trip
  4. WHERE ID_comp<3
  5. ORDER 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
  1. SELECT row_number() over(partition BY id_comp ORDER BY id_comp,trip_no) num,
  2. trip_no, id_comp
  3. FROM trip
  4. WHERE ID_comp < 3
  5. ORDER 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

Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
St. Louis Hardwood Floors . digital lab notebook
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100