World Wide Tutorial

Preamble
Given the problems that I regularly ask colleagues and users, I have tried to solve the case of a family problems rather difficult to express in SQL simple. I came to the conclusion that it was interesting to foresee adding a new SQL join type, the seam "LINEAR". We will as a first step pose different problems and see how we can solve this by introducing new operator.

In the immediate I do not venture into the relational algebra to determine whether the good doctor Codd has failed at its task. I therefore reserve the theorists a great battle in perspective to determine whether this operation and how to represent ...
PLEASE: you can have the english translation of this paper by clicking HERE
1. Number lines and all queries arising
Either the table T_CLIENT_CLI (CLI_ID, CLI_NOM) as follows:
CLI_ID CLI_NOM
------- ------------
17 DURAND
192 DUPONT
44 DUVAL
11 DUMOULIN
741 DULIN
82 DUPOND
177 DURAND
Creation of the test Thursday:
CREATE TABLE T_CLIENT_CLI (CLI_ID INTEGER, CLI_NOM VARCHAR (10)) INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM) VALUES (17, 'DURAND') INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM) VALUES (192, 'DUPONT') INSERT INTO T_CLIENT_CLI (CLI_ID , CLI_NOM) VALUES (44, 'DUVAL') INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM) VALUES (11, 'DUMOULIN') INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM) VALUES (741, 'DULIN') INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM ) VALUES (82, 'DUPOND') INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM) VALUES (177, 'DURAND')

The question is: how to get a response the names of our customers in alphabetical order with their rank (from 1 to n)?

1.1. RESPONSE 1
If we apply strictly the issue, then the answer is:
CLI_NOM RANG
------------- -----
DULIN 1
DUMOULIN 2
DUPOND 3
DUPONT 4
DURAND 5
DURAND 5
DUVAL 7
Indeed, both located DURAND tie occupy the 5th place, while Duval has not the 6th, but the 7th row!

1.2. ANSWER 2
Another possible solution is:
CLI_NOM RANG
------------- -----
DULIN 1
DUMOULIN 2
DUPOND 3
DUPONT 4
DURAND 5
DURAND 6
DUVAL 7
This means a frank and direct numbering regardless of duplication or ambiguity selected information. It's a bit what are the columns of auto increment of certain RDBMS.

1.3. RESPONSE 3
Finally we can refine the latter solution by introducing a counting to eliminate duplication:

CLI_NOM RANG NUMBER
------------- ----- ------
DULIN 1 1
DUMOULIN 2 1
DUPOND 3 1
DUPONT 4 1
DURAND 5 2
DUVAL 7 1
Who deserves to be more clean match, however, the initial application!

1.4. ANSWER 4
By pushing things to extremes, it may require that the numbering category is strict and without "hole", like this:
CLI_NOM RANG NUMBER
------------- ----- ------
DULIN 1 1
DUMOULIN 2 1
DUPOND 3 1
DUPONT 4 1
DURAND 5 2
DUVAL 6 1
But what are the applications necessary to achieve the various solutions proposed?

1.5. Queries
In principle, requests to meet such demand require auto non equivalent to join the enumeration of tuples whose values above the tuple underway.

Query the response 1:
SELECT T1.CLI_NOM, COUNT (T2.CLI_ID) + 1 AS RANK FROM T_CLIENT_CLI T1 LEFT OUTER JOIN T_CLIENT_CLI T2 ON T1.CLI_NOM> T2.CLI_NOM GROUP BY T1.CLI_ID, T1.CLI_NOM ORDER BY ROW

Query the answer 2:
SELECT T1.CLI_NOM, COUNT (T2.CLI_ID) + 1 AS RANK FROM T_CLIENT_CLI T1 LEFT OUTER JOIN T_CLIENT_CLI T2 ON T1.CLI_NOM | | CAST (T1.CLI_ID AS CHAR (16))> T2.CLI_NOM | | CAST (T2. CLI_ID AS CHAR (16)) GROUP BY T1.CLI_ID, T1.CLI_NOM ORDER BY ROW

Query the response 3:
SELECT T1.CLI_NOM, RANK, NAME FROM (SELECT DISTINCT T1.CLI_NOM, COUNT (T2.CLI_ID) + 1 AS RANK FROM T_CLIENT_CLI T1 LEFT OUTER JOIN T_CLIENT_CLI T2 ON T1.CLI_NOM> T2.CLI_NOM GROUP BY T1.CLI_ID, T1. CLI_NOM) T1 INNER JOIN (SELECT CLI_NOM, COUNT (CLI_ID) AS NUMBER FROM T_CLIENT_CLI T1 GROUP BY CLI_NOM) Q2 ON T1.CLI_NOM = T2.CLI_NOM ORDER BY ROW

Query the response 4:
SELECT T2.CLI_NOM, COUNT (T1.CLI_NOM) AS RANK, NAME FROM (CLI_NOM SELECT DISTINCT FROM T_CLIENT_CLI) T1 LEFT OUTER JOIN (CLI_NOM SELECT DISTINCT FROM T_CLIENT_CLI) Q2 ON T1.CLI_NOM <= T2.CLI_NOM INNER JOIN (SELECT CLI_NOM, COUNT (CLI_ID) AS NUMBER FROM T_CLIENT_CLI GROUP BY CLI_NOM) T3 ON T2.CLI_NOM = T3.CLI_NOM GROUP BY T2.CLI_NOM, T3.NOMBRE ORDER BY ROW

The least we can say is that this kind of requests is not in the spirit of starting. What then developer facing this problem in a context of tables far more extensive than our example? It is very likely that the latter passes by hand and lunges a nice stored procedure at best and at worst on the client!

CREATE TABLE T_I_ENT (ENT_I INTEGER) INSERT INTO T_I_ENT (ENT_I) VALUES (0) INSERT INTO T_I_ENT (ENT_I) VALUES (1) INSERT INTO T_I_ENT (ENT_I) VALUES (2) INSERT INTO T_I_ENT (ENT_I) VALUES (3) INSERT INTO T_I_ENT (ENT_I) VALUES (4) INSERT INTO T_I_ENT (ENT_I) VALUES (5) INSERT INTO T_I_ENT (ENT_I) VALUES (6) INSERT INTO T_I_ENT (ENT_I) VALUES (7) INSERT INTO T_I_ENT (ENT_I) VALUES (8) INSERT INTO T_I_ENT (ENT_I) VALUES (9) INSERT INTO T_I_ENT (ENT_I) VALUES (10) ... INSERT INTO T_I_ENT (ENT_I) VALUES (1000)

We note in passing that it is unnecessary to enter all the numbers from 1 to 1000, the first ten enough and a simple application integration will play the role of complementary insertion:
INSERT INTO T_I_ENT (ENT_I) SELECT DISTINCT I1.ENT_I + (I2.ENT_I * 10) + (I3.ENT_I * 100) FROM T_I_ENT I1 CROSS JOIN T_I_ENT I2 CROSS JOIN T_I_ENT I3 CROSS JOIN T_I_ENT I4 WHERE I1.ENT_I + (I2. ENT_I * 10) + (I3.ENT_I * 100) BETWEEN 1000 AND 0

Thus the juxtaposition of the projection by the name of the customer table ordered by the customer with the projection of the whole table ordered meets our expectations:
CLI_NOM T_I_ENT
---------- ------------
DULIN 1
DUMOULIN 2
DUPOND 3
DUPONT 4
DURAND 5
DURAND 6
DUVAL 7
That is why I propose the new operator join linear: LINEAR JOIN to match the line of a row of the table left, the line rang a + offset the table right and so forth.

4. Syntax and rules of the seam line
The seam line meets the following syntax:
SELECT <liste of selection> FROM <table left [LEFT | RIGHT] LINEAR JOIN <table droite> [OFFSET <offset value>]

In our previous example, you just have to do:
SELECT CLI_NOM, T_I_ENT FROM LINEAR T_CLIENT_CLI JOIN T_I_ENT OFFSET 1 / * Elimination of the first line, a zero * / ORDER BY CLI_NOM, T_I_ENT

Some explanations:
the key word OFFSET can tell from what line to take into account the first row of the table right associated with the first line of the table left
ORDER BY clause operates separately before the joint linear tables attached the joint can be left outside (LEFT JOIN LINEAR) or right (RIGHT JOIN LINEAR), but not on both sides. By default it is internal

Example join linear external right:
SELECT CLI_NOM, T_I_ENT FROM RIGHT T_CLIENT_CLI LINEAR JOIN T_I_ENT ORDER BY CLI_NOM, T_I_ENT

which gives:
CLI_NOM T_I_ENT
---------- ------------
DULIN 0
DUMOULIN 1
DUPOND 2
DUPONT 3
DURAND 4
DURAND 5
DUVAL 6
NULL 7
NULL 8
...
NULL 1000
To solve our problem of allocating places theatre, just do:
SELECT CLI_NOM, PLC_REF FROM LINEAR T_CLIENT_CLI JOIN T_I_ENT LINEAR JOIN T_PLACE_PLC ORDER BY CLI_NOM, T_I_ENT, PLC_REF

I do not know what you think but I find this writing simple and easy to understand!

5. CONCLUSION
These complaints are similar to the T-JOIN (théta joints) Dr. Codd to obtain an optimal correspondence inequalities (typically the problem of allocating students in rooms capacity data).
I leave it to your sagacity representation of such a joint in relational algebra!