[Solved] Query that generates a progressive numbering

[charlie.it]

Hi, I found this query online. It works, but I would like to understand it. Who helps me?
test is a table with ID not in progressive order (2, 5, 3, 7, 1 ....), the query generates a progressive ID1 numbering each record (1, 2, 3, 4, 5).
In particular, what is t2? I do not have tables named t2. How the SQL code works ... FROM "test" "t2" ... without the comma in the middle?

Code: Select all

SELECT "ID", "name", ( SELECT COUNT( * ) + 1 FROM "test" "t2" WHERE "t2"."ID" < "test"."ID" ) "ID1" FROM "test"

Thanks in advance.

[robleyd]

I believe t2 is an alias for test.

[charlie.it]

I also though it, but I can't write "test" ALIAS "t2", it does't work.
Thank you for your answer.

[keme]

The optional second name will be the alias. The ALIAS or AS keyword is also optional (but recommended for legibility). It may be (strangely...) that the ALIAS keyword is not implemented on your SQL server. Did you try AS?
The alias is required for this method of counting, so you have two "virtual instances" of the "test" table and can compare each record to every other record of the table.
This makes a "tight set" for numbering when the set of values in the "ID" field has gaps (unused numbers in between), but I can't explain why this makes an ordered sequence out of an unordered string of IDs without using ORDER BY.

[charlie.it]

Thaks @keme, I tried "AS" but embedded HSQLDB reject it like "ALIAS".
I attach a file for who will wish try.

[Villeroy]

Your query does not work because there is no column "nome". The column is called "name".
I added "nome" as an alias for "name" and the optional AS keywords

Code: Select all

SELECT "id", "name" AS "nome", ( SELECT COUNT( * ) + 1 FROM "test" AS "t2" WHERE "t2"."id" < "test"."id" ) "Progressivo" FROM "test"

The AS keyword is optional

Code: Select all

SELECT "name" AS "nome"
FROM "test" AS "t1" 

can be written without AS as

Code: Select all

SELECT "name" "nome"
FROM "test" "t1" 

[chrisb]

i see that @Villeroy has already responded & provided answers/suggestions based on the uploaded db.
my response is more general & based on the initial post made by @charlie.it.

essentially we have two queries an outer query & a correlated sub-query.
a correlated sub-query is a query nested within another query that uses values from that other query.

outer query:

Code: Select all

SELECT "ID", "name" FROM "test"

correlated sub-query:

Code: Select all

( SELECT COUNT( * ) + 1 FROM "test" "t2" WHERE "t2"."ID" < "test"."ID" ) "ID1"

both outer & inner queries select data from the very same single table "test”.
the outer query is self explanatory.
the sub-query simply counts the number rows in the table "test" where value of "ID" is less than the currently selected value of "ID" in the outer query.
because both outer & inner queries reference the same table it's necessary to assign an alias "t2" to the table of the inner query in order to distinguish them.
Your query could be coded as below.
Note: the 'AS' keyword is optional.

Code: Select all

SELECT "ID", "name", ( SELECT COUNT( * ) FROM "test" AS "t2" WHERE "t2"."ID" <= "test"."ID" ) "ID1" FROM "test"

if "ID" = 1, 5, 9 then the sub-query will return 1, 2, 3.
if "ID" = 1, 5, 5, 9 then the sub-query will return 1, 3, 3, 4.
so provided the value of "ID" is unique the sub-query always returns a sequential result equivalent to table row number ordered by "ID" & is very often used to self join a table when using the default embedded hsql 1.8.0.10 which lacks the rownum() function.

[Sliderule]

Just to help make this as clear as mud ( or should I say fog ), when using something like above, keep in mind that if your database is being used in a server / multi-user environment, ¿ Another user, at that split second, may have INSERTed, DELETEDed, or, UPDATEd a record, therefore, the values you found a few seconds ago may not now be appropriate.

That is the reason, with a sophisticated database back-end ( such as HSQL 2.X , NOT the Embedded version 1.8.0.10 ) the use of CREATE SEQUENCE or a TRIGGER to perform some data manipulation prior to, or, after data manipulation can be helpful.

Sliderule

[Villeroy]

Less elegant because the result set is not editable:

Code: Select all

SELECT "t1"."id", "t1"."name" "nome", COUNT( "t2".* ) "Progressivo" 
FROM "test" "t1", "test" "t2" 
WHERE "t1"."id" > "t2"."id" 
GROUP BY "t1"."id", "nome"

[charlie.it]

I thank everyone for the precious help and especially @chrisb for his comprehensive explanation .