19:25

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Typical problems

Numbering of a query result set

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

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):

Console

Execute

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.

Console

Execute

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;

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

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

Pages

COUNT Ranking functions ROW_NUMBER numbering primary key

Previous [Numbering of a query result set]

[Numbering rows when there are duplicates in the results column] Next

Last added:

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 Problems and solutions

S. I. Moiseenko

Typical problems

Numbering of a query result set

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