change variables within select statement
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
Dear Christian There are no plans for such an 'enhancement' foreseen. regards, Martin On 8/15/13 6:31 PM, Christian Kleinke wrote:
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
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Hi Christian, what abotu using standard(?) SQL ROW_NUMBER() instead? E.g., sql>create table mytab as select id, schema_id from tables order by schema_id with data; operation successful (1.404ms) sql>select * from mytab; +------+-----------+ | id | schema_id | +======+===========+ | 2001 | 2000 | | 2006 | 2000 | | 2015 | 2000 | | 2024 | 2000 | | 2032 | 2000 | | 2042 | 2000 | | 2046 | 2000 | | 2055 | 2000 | | 2064 | 2000 | | 2075 | 2000 | | 2082 | 2000 | | 2087 | 2000 | | 2098 | 2000 | | 5276 | 2000 | | 5293 | 2000 | | 6059 | 2000 | | 6100 | 2000 | | 6122 | 2000 | | 6138 | 2000 | | 5060 | 2000 | | 5070 | 2000 | | 5090 | 2000 | | 5098 | 2000 | | 5102 | 2000 | | 5105 | 2000 | | 5109 | 2000 | | 5262 | 2000 | | 2146 | 2102 | | 2135 | 2102 | | 2130 | 2102 | | 2123 | 2102 | | 2112 | 2102 | | 2103 | 2102 | +------+-----------+ 33 tuples (0.894ms) sql>select id, schema_id, row_number() over (partition by schema_id) as pos from mytab; +------+-----------+------+ | id | schema_id | pos | +======+===========+======+ | 2001 | 2000 | 1 | | 2006 | 2000 | 2 | | 2015 | 2000 | 3 | | 2024 | 2000 | 4 | | 2032 | 2000 | 5 | | 2042 | 2000 | 6 | | 2046 | 2000 | 7 | | 2055 | 2000 | 8 | | 2064 | 2000 | 9 | | 2075 | 2000 | 10 | | 2082 | 2000 | 11 | | 2087 | 2000 | 12 | | 2098 | 2000 | 13 | | 5276 | 2000 | 14 | | 5293 | 2000 | 15 | | 6059 | 2000 | 16 | | 6100 | 2000 | 17 | | 6122 | 2000 | 18 | | 6138 | 2000 | 19 | | 5060 | 2000 | 20 | | 5070 | 2000 | 21 | | 5090 | 2000 | 22 | | 5098 | 2000 | 23 | | 5102 | 2000 | 24 | | 5105 | 2000 | 25 | | 5109 | 2000 | 26 | | 5262 | 2000 | 27 | | 2146 | 2102 | 1 | | 2135 | 2102 | 2 | | 2130 | 2102 | 3 | | 2123 | 2102 | 4 | | 2112 | 2102 | 5 | | 2103 | 2102 | 6 | +------+-----------+------+ 33 tuples (1.159ms) Best, Stefan On Thu, Aug 15, 2013 at 06:31:36PM +0200, Christian Kleinke wrote:
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
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
Hi Stefan,
thanks for this hint. It helps me to find a much simpler way to solve my
problem - I used the dense_rank() function in combination with the
partition statement for it now.
Regards,
Christian
2013/8/19 Stefan Manegold
Hi Christian,
what abotu using standard(?) SQL ROW_NUMBER() instead?
E.g.,
sql>create table mytab as select id, schema_id from tables order by schema_id with data; operation successful (1.404ms) sql>select * from mytab; +------+-----------+ | id | schema_id | +======+===========+ | 2001 | 2000 | | 2006 | 2000 | | 2015 | 2000 | | 2024 | 2000 | | 2032 | 2000 | | 2042 | 2000 | | 2046 | 2000 | | 2055 | 2000 | | 2064 | 2000 | | 2075 | 2000 | | 2082 | 2000 | | 2087 | 2000 | | 2098 | 2000 | | 5276 | 2000 | | 5293 | 2000 | | 6059 | 2000 | | 6100 | 2000 | | 6122 | 2000 | | 6138 | 2000 | | 5060 | 2000 | | 5070 | 2000 | | 5090 | 2000 | | 5098 | 2000 | | 5102 | 2000 | | 5105 | 2000 | | 5109 | 2000 | | 5262 | 2000 | | 2146 | 2102 | | 2135 | 2102 | | 2130 | 2102 | | 2123 | 2102 | | 2112 | 2102 | | 2103 | 2102 | +------+-----------+ 33 tuples (0.894ms) sql>select id, schema_id, row_number() over (partition by schema_id) as pos from mytab; +------+-----------+------+ | id | schema_id | pos | +======+===========+======+ | 2001 | 2000 | 1 | | 2006 | 2000 | 2 | | 2015 | 2000 | 3 | | 2024 | 2000 | 4 | | 2032 | 2000 | 5 | | 2042 | 2000 | 6 | | 2046 | 2000 | 7 | | 2055 | 2000 | 8 | | 2064 | 2000 | 9 | | 2075 | 2000 | 10 | | 2082 | 2000 | 11 | | 2087 | 2000 | 12 | | 2098 | 2000 | 13 | | 5276 | 2000 | 14 | | 5293 | 2000 | 15 | | 6059 | 2000 | 16 | | 6100 | 2000 | 17 | | 6122 | 2000 | 18 | | 6138 | 2000 | 19 | | 5060 | 2000 | 20 | | 5070 | 2000 | 21 | | 5090 | 2000 | 22 | | 5098 | 2000 | 23 | | 5102 | 2000 | 24 | | 5105 | 2000 | 25 | | 5109 | 2000 | 26 | | 5262 | 2000 | 27 | | 2146 | 2102 | 1 | | 2135 | 2102 | 2 | | 2130 | 2102 | 3 | | 2123 | 2102 | 4 | | 2112 | 2102 | 5 | | 2103 | 2102 | 6 | +------+-----------+------+ 33 tuples (1.159ms)
Best, Stefan
On Thu, Aug 15, 2013 at 06:31:36PM +0200, Christian Kleinke wrote:
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
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Christian Kleinke -
Martin Kersten -
Stefan Manegold