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

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:

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

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:

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:

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

To solve the problem on SQL-EX.RU

Let’s add a some difficulty to the task and try to number the models of each maker separately. We’ll take the above solution and introduce the following changes:

1. Appending the equality of makers to the join condition with aids to unite the models of each maker into separate group.

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

That’s all in principle, but the result is not an illustrative one.

2. Adding a maker into SELECT list, in so doing this is not the matter which table it will be taken from in view of makers equality. But the column must be presented in the GROUP BY clause also (MySQL is not a case):

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

3. At last, for illustrative purposes we’ll use sorting. The maker column should be the first sorting column to deduce each group separately.

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

I hope you will not encounter any difficulty while numbering models over product types. Do it by yourself as an exercise.

Naturally, ranking functions make the query much more simple.

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