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:
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:
Here's the result of execution of this query:
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: