Typical problems

Numbering of a query result set

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;

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

    Scheme

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;

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

    Scheme

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:

Console

Execute

SELECT ROW_NUMBER() OVER(ORDER BY model) no, model
FROM Product;

SELECT ROW_NUMBER() OVER(ORDER BY model) no, model FROM Product;

    Scheme