@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