Dear all,

is there a possibility to change declared variables in monetdb within a select statement.

For example in MySQL the variables can be declared with the @-symbol, and such variables easily could be changed in a case-when condition.

Here a little example of what I want to do (in MySQL syntax):

SET @id = '';
SET @pos = 1;

drop table if exists test_table;
create table test_table as
select
*,
if (@id<> id, @pos:=1, @pos:=@pos+1) as position_number,
@id:=id,
from
table_with_ids;

Here the rows with identical ids should be counted, and if the id changes the position_number should start again as zero.

If somebody has a hint for me, to execute such a query in monetdb, I would be very glad.

Greetings,

Christian