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