Hi, how can i get a rowid starting at 1 for current select only? The __rowid is a unique row-number in the table but we need a numbering for current select statement only, starting at 1 ...

Hints are welcome.
Yusuf Zorlu
MicrotronX

As i see i can use RUNSUM(1) but this works only with GROUP BY, we need this without GROUP BY, possible?
Yusuf Zorlu
MicrotronX

Yusuf


The only single statement way I can see to do it is if there's a column in the selection that is unique. If so group by that column.

Looking back in the newsgroup it appears that runsum used to work without a group by clause, and looking at the on-line help <<The RUNSUM function returns the running sum of a given numeric or interval expression for all selected rows. The selected rows can be grouped into logical sub-sets by using the GROUP BY clause of the SELECT statement. Any time the numeric or interval expression is NULL, it is excluded from the running sum calculation.>> I'm not sure it should be necessary.

Best idea is to email Tim directly.

Roy Lambert

On 6/6/2019 10:41 AM, Yusuf Zorlu wrote:
> Hi, how can i get a rowid starting at 1 for current select only? The __rowid is a unique row-number in the table but we need a numbering for current select statement only, starting at 1 ...
>
> Hints are welcome.

You could try something like this (assuming your table is called MyTable) :

select runsum(1) as RowID, MyTable.* from MyTable Group by "__rowid"

or if you need something with where filter then

select runsum(1) as RowID, MyTable.* from MyTable where
MyTable.SomeField='SomeValue' Group y by "__rowid"


Raul

Raul


Sneaky - I didn't even thing of __RowID - in truth I'd forgotten it again.

Roy Lambert

Yusuf,

<< As i see i can use RUNSUM(1) but this works only with GROUP BY, we need this without GROUP BY, possible? >>

Like all aggregate functions, you can use RUNSUM without a GROUP BY.

If you have an example where it isn't working correctly in such a context, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Yusuf,

I think the confusion may be in that you're trying to use RUNSUM() in a non-grouped, non-single-row-result set context.  Aggregate functions only work in the context of grouped (GROUP BY) or single-row result sets.  If you use them in another context, you will get an error about a missing GROUP BY due to the other non-aggregate expressions.

Raul's answer is correct (and brilliant - I wouldn't have thought of it): just use the __RowID to cause a grouping on each row.

Tim Young
Elevate Software
www.elevatesoft.com

Raul wrote:

<< select runsum(1) as RowID, MyTable.* from MyTable Group by "__rowid"


Thanks Raul, this is a perfect solution and works for all cases.
Yusuf Zorlu
MicrotronX

Tim


>If you have an example where it isn't working correctly in such a context, please let me know.


select runsum(1), * from members

or

select runsum(1), _ID from members

ElevateDB Error #700 An error was found in the statement at line 1 and column 19 (Invalid expression "members"."_Notes" found, GROUP BY clause required for any column references)

ANSI 2.31b5 W7

Roy

Tim

Just to be totally correct

select runsum(1), * from members

ElevateDB Error #700 An error was found in the statement at line 1 and column 19 (Invalid expression "members"."_Notes" found, GROUP BY clause required for any column references)


select runsum(1), _ID from members

ElevateDB Error #700 An error was found in the statement at line 1 and column 19 (Invalid expression "_ID" found, GROUP BY clause required for any column references)


CREATE TABLE "Members"
(
"_ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"_Forename" VARCHAR(15) COLLATE "ANSI_CI",
"_Surname" VARCHAR(30) COLLATE "ANSI_CI",
"_Role" VARCHAR(50) COLLATE "ANSI_CI" NOT NULL,
"_DateJoined" DATE,
"_DateLeft" DATE,
"_fkBanks" INTEGER,
"_PhoneNumber" VARCHAR(15) COLLATE "ANSI_CI",
"_EMail" VARCHAR(60) COLLATE "ANSI_CI",
"_Active" BOOLEAN DEFAULT TRUE NOT NULL,
"_Address" CLOB COLLATE "ANSI_CI",
"_Notes" CLOB COLLATE "ANSI_CI",
CONSTRAINT "PK" PRIMARY KEY ("_ID"),

Roy