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 <Stefan.Manegold@cwi.nl>
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