[Solved] Query that generates a progressive numbering

Creating tables and queries

[Solved] Query that generates a progressive numbering

Postby charlie.it » Wed Sep 25, 2019 7:47 am

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   Expand viewCollapse view
SELECT "ID", "name", ( SELECT COUNT( * ) + 1 FROM "test" "t2" WHERE "t2"."ID" < "test"."ID" ) "ID1" FROM "test"


Thanks in advance.
Last edited by charlie.it on Wed Sep 25, 2019 10:49 pm, edited 1 time in total.
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: Query that generates a progressive numbering

Postby robleyd » Wed Sep 25, 2019 8:26 am

I believe t2 is an alias for test.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3456
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Query that generates a progressive numbering

Postby charlie.it » Wed Sep 25, 2019 8:38 am

I also though it, but I can't write "test" ALIAS "t2", it does't work.
Thank you for your answer.
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: Query that generates a progressive numbering

Postby keme » Wed Sep 25, 2019 9:13 am

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.
User avatar
keme
Volunteer
 
Posts: 3365
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Query that generates a progressive numbering

Postby charlie.it » Wed Sep 25, 2019 10:44 am

Thaks @keme, I tried "AS" but embedded HSQLDB reject it like "ALIAS".
I attach a file for who will wish try.
Attachments
Progressivo.odb
(3.77 KiB) Downloaded 47 times
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: Query that generates a progressive numbering

Postby Villeroy » Wed Sep 25, 2019 12:48 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
SELECT "name" AS "nome"
FROM "test" AS "t1"

can be written without AS as
Code: Select all   Expand viewCollapse view
SELECT "name" "nome"
FROM "test" "t1"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28536
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query that generates a progressive numbering

Postby chrisb » Wed Sep 25, 2019 2:17 pm

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   Expand viewCollapse view
SELECT "ID", "name" FROM "test"

correlated sub-query:
Code: Select all   Expand viewCollapse view
( 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   Expand viewCollapse view
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.
open office 4.1.7 & LibreOffice 6.3.6.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 227
Joined: Mon Jun 07, 2010 4:16 pm

Re: Query that generates a progressive numbering

Postby Sliderule » Wed Sep 25, 2019 3:51 pm

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
User avatar
Sliderule
Volunteer
 
Posts: 1242
Joined: Thu Nov 29, 2007 9:46 am

Re: Query that generates a progressive numbering

Postby Villeroy » Wed Sep 25, 2019 7:32 pm

Less elegant because the result set is not editable:
Code: Select all   Expand viewCollapse view
SELECT "t1"."id", "t1"."name" "nome", COUNT( "t2".* ) "Progressivo"
FROM "test" "t1", "test" "t2"
WHERE "t1"."id" > "t2"."id"
GROUP BY "t1"."id", "nome"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28536
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query that generates a progressive numbering

Postby charlie.it » Wed Sep 25, 2019 10:48 pm

I thank everyone for the precious help and especially @chrisb for his comprehensive explanation :super: .
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest