loading..
   English
14:29

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

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.

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

Console
Execute
  1. SELECT COUNT(*) no, P1.maker, P2.model
  2. FROM Product P1 JOIN
  3. Product P2 ON P1.maker =P2.maker AND P1.model <= P2.model
  4. GROUP BY P1.maker, P2.model
  5. 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.

Console
Execute
  1. SELECT COUNT(*) no, P1.maker, P2.model
  2. FROM Product P1 JOIN
  3. Product P2 ON P1.maker =P2.maker AND P1.model <= P2.model
  4. GROUP BY P1.maker, P2.model
  5. ORDER BY P1.maker, P2.model;

no maker model
1 A 1232
2 A 1233
3 A 1276
4 A 1298
5 A 1401
6 A 1408
7 A 1752
1 B 1121
2 B 1750
1 C 1321
1 D 1288
2 D 1433
1 E 1260
2 E 1434
3 E 2112
4 E 2113

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.

Console
Execute
  1. SELECT ROW_NUMBER() OVER(PARTITION BY maker ORDER BY model) no, maker, model
  2. FROM Product
  3. ORDER BY maker, model;

Bookmark and Share
Pages 1 2
Tags
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 CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.