12:12

# Numbering of rows in accordance with the order of values of a primary key page 1

Of course, rows should be numbered in accordance with some order. Let this order to be set by the primary key column, that is in order of increase (or decrease) of values in this column. Let us suppose for distinctness that we must renumber models in the table Product, where the model number is a primary key. The important thing is that values of primary key can't contain duplicates and NULL values, because of which there's a principled possibility to set one-to-one accordance between the number of the model and number of the row in order of sorting the models.

First, let's consider the following query:

Console
Execute
`SELECT P1.model, P2.model FROM Product P1 JOIN  Product P2 ON P1.model <= P2.model;`

Here the joining of two identical tables on the inequality P1.model <= P2.model is performing, consequently each model from the second table (P2.model) will join only with the models from the first table (P1.model), numbers of which are less or equal the number of this model. Consequently we'll get that the model with minimal number (1121) will be present in the second row of resulting set only once, as it less or equal only to itself . On the other end will be the model with maximal number, as any model is less or equal to it. Hence, the model with maximal number will match with every model, and number of such matches is equal to overall number of models in the table Product.

It's clearly that this number of each model occurring in the second row of resulting set is exactly an ordinal number of the model in sorting models ascending.

Thus, to solve our task of numbering it's enough to count models in the right row, that is easily can be done by grouping and using aggregate function COUNT:

Solution 8.2.1

Console
Execute
`SELECT COUNT(*) no, P2.model FROM Product P1 JOIN  Product P2 ON P1.model <= P2.model GROUP BY P2.model;`

Here's the result of execution of this query:

no model
1    1121
2    1232
3    1233
4    1260
5    1276
6    1288
7    1298
8    1321
9    1401
10    1408
11    1433
12    1434
13    1750
14    1752
15    2112
16    2113

In order to number in inverse order it's enough to change the sign of inequality to the opposite.

If DBMS that you use supports ranking functions, the numbering of rows can be done absolutely simply:

Console
Execute
`SELECT ROW_NUMBER() OVER(ORDER BY model) no, modelFROM Product;`

To solve the problem on SQL-EX.RU

 Pages 1 2
Tags
The book was updated
several days ago