How use SELECT COUNT as a substitute for ROW_NUMBER

[SonomaCharles]

@RobertG I am trying to use your suggestion to use SELECT COUNT instead of WITH ROW_NUMBER in Why "Access is denied: ROW_NUMBER( ..."? - #16 by CRDF .

I have now upgraded to HSQLDB external v2.7 so I have ROW_NUMBER() but I am trying to get around the restriction on WITH + Prompting detailed here How create a Query with both a Prompt and WITH - #2 by Wanderer

Your suggested use of COUNT does not give me what I require. I have rows with a “Sequence” column. I want to convert the values in Sequence into an integer “Order” such that the integers are (a.) in the same order as Sequence, (b.) strictly in the sequence 1, 2, 3, 4, … except that (c) rows with the same Sequence get the same Stop.

The following accomplishes that but requires WITH which precludes the use of prompts.

WITH "sequence_rank" AS (
SELECT "Sequence", ROW_NUMBER ( ) OVER ( ) AS "Stop_Num" FROM ( SELECT DISTINCT "Sequence" FROM "Clients" WHERE "Route" = 'B'  )  )
SELECT "c"."Route", "s"."Stop_Num" AS "Order", "c"."First", "c"."Last", 
FROM "Clients" AS "c" 
JOIN "sequence_rank" AS "s" 
    ON ("c"."Sequence" = "s"."Sequence" )

For example, Sequence values of 10, 20, 20, 20, 30 would yield Order values of 1, 2, 2, 2, 3.

Is it possible to do that with SELECT COUNT?

Thanks much!
Charles

[SonomaCharles]

Do you see anything wrong with this? It seems to work.

SELECT "c"."Route", "c"."First", "c"."Sequence", ( SELECT COUNT( DISTINCT "Sequence" ) FROM "Clients" WHERE "Route" = "c"."Route" AND "Sequence" <= "c"."Sequence" ) AS "Order" FROM "Clients" AS "c" WHERE "c"."Route" = UPPER ( :Please_Enter_Route ) ORDER BY "Order" ASC

Here is sample output showing Sequence and Order and a little bit of data out of the table.

Charles

[CRDF]

What’s the point of mapping sequence to non unique “row_number” (order)

[SonomaCharles]

Because that’s what the user wants?

The output is a “route sheet” for volunteer drivers. It shows clients by stop number. So if there were two clients at one address the stops might be 1, 2, 2, 3, …

I have been assigning the stop number manually. It is a PITA because if you add a client you have to “push down” all of the clients after the one you add. Ditto if you delete a client. (They want the stop numbers to be 1, 2, 3, … with no gaps.)

It is about to be even more of a PITA because soon some clients will get meals MTWTF, some MWF and some Tu-Th. There will be two route sheets, one for MWF drivers and one for Tu-Th drivers. So there would need to be TWO stop numbers, one for MWF and one for Tu-Th. (A given client might be the third stop on MWF but the fourth stop on T-Th.)

So I have changed the logic to have a “sequence number” that is not necessarily sequential 1, 2, 3 but from which the query will assign a sequential stop number. The sequence of clients will remain constant across days but the stop numbers might be different.

If you add a client you can just slip them in between two other clients’ sequence numbers, and if you delete a client there is no re-ordering to do.

Aren’t you glad you asked … Sorry if this is still not clear.

Charles

[CRDF]

Of course I must explain myself.
As you mentioned

I want to use this query in a report

what I had in my mind was something like
 
SELECT Sequence, Client, MainAddress, Complement […] ORDER BY Sequence;
 
So producing the Report as something like
Grouping: by Sequence
Group header: [MainAddress]
Detail: [Complement: apt, unit…] [Client]
 
But of course users practice dictates what best suits.

[Villeroy]

In my PowerFilter.odb save the following query as “Query1”:

SELECT "D1"."ID", COUNT( "D2"."V" ) AS "Count" 
FROM "Data" AS "D1" 
    JOIN "Data" AS "D2" ON "D1"."PID" = "D2"."PID" AND "D1"."V" >= "D2"."V" 
GROUP BY "D1"."ID"

This creates a 2-column row set with the primary key “ID” and how many values “V” are smaller than or equal to the “V” of another instance of the same table within the set having the same person ID “PID”.
Create a second query “Query2”

SELECT "Data".*, "Query1"."Count" 
FROM "Query1" JOIN "Data" 
ON "Query1"."ID" = "Data"."ID"

selects all columns with the respective count of records having a smaller value within the same group. Put this in a subform filtered by the main form PID and sort any way you want.

You can merge this in one query:

SELECT "Data".*, "Query1"."Count" FROM (
    SELECT "D1"."ID", COUNT( "D2"."V" ) AS "Count" 
    FROM "Data" AS "D1" 
    JOIN "Data" AS "D2" ON "D1"."PID" = "D2"."PID" AND "D1"."V" >= "D2"."V" 
    GROUP BY "D1"."ID"
)AS"Query1"
JOIN "Data" ON "Query1"."ID" = "Data"."ID"

which might be the same as

WITH  "Query1" AS (
    SELECT "D1"."ID", COUNT( "D2"."V" ) AS "Count" 
    FROM "Data" AS "D1" 
    JOIN "Data" AS "D2" ON "D1"."PID" = "D2"."PID" AND "D1"."V" >= "D2"."V"
    GROUP BY "D1"."ID"
)
SELECT "Data".*, "Query1"."Count" 
FROM "Query1" 
JOIN "Data" ON "Query1"."ID" = "Data"."ID"

EDIT: The last query with WITH works in direct mode with HSQL 2.4.1.

[SonomaCharles]

@Villeroy thank you all for your invaluable help. I quite seriously could not be doing this without your help.

Charles

[cpb]

@SonomaCharles,
at last we get some idea as to the makeup of your data (post #3 2 Feb 21:22).
I apologise the delay but in the interest of clarity wish to point out that ROWNUM() (row_number) can be used within a parameter query.
to prove the point here is a simple parameter query using ROWNUM().
it’s much more efficient than using COUNT within a subquery.

select
	c."Route",
	c."First",
	c."Sequence",
	b."Order"
from
(
	select
		a.*,
		rownum() "Order"
	from
	(
		select distinct
			"Route", 
			"Sequence"
		from
			"Clients"
		where
			"Route" = upper(:route)
	) a
) b
join
	"Clients" c
	on b."Route" = c."Route" and b."Sequence" = c."Sequence"
order by
	"Sequence"

[SonomaCharles]

@cpb okay, thanks.

As a veteran of a lot of large systems work I get how inefficient it is what I am doing. I loop over the data n*(n-1) or so times.

The worst case is 18 clients per route, however, so that is only 306 passes over the data, and no one seems to care about CPU cycles on personal machines. The response time for the query is more or less instantaneous.

Good to know about ROW_NUMBER. I was really struggling to understand what HSQLDB could and could not do. I failed to grasp that the internal database was locking me into a ten-year-old version that of course did not correspond to the on-line documentation. The SQL Direct option versus the GUI also confused me as to what I could and could not do.

Charles

[Villeroy]

@SonomaCharles

ten-year-old version

HSQL 1.8 is 20 years old.

[SonomaCharles]

@Villeroy

HSQL 1.8 is 20 years old.

Rounding error