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:

  1. SELECT P1.model, P2.model
  2. FROM Product P1 JOIN
  3. 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

  1. SELECT COUNT(*) no, P2.model
  2. FROM Product P1 JOIN
  3. Product P2 ON P1.model <= P2.model
  4. 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:

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

To solve the problem on SQL-EX.RU

Bookmark and Share
Pages 1 2
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
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100