On 6/23/2011 11:37 AM, Jan Hudec wrote:
> Is there a way to do group by query with well defined order of calls to the
> aggregate function?
Not reliably.
> So I want to denormalize the database by doing:
>
> create temporary table packed_array (
> obj_id integer primary key,
> values text);
> insert into packed_array (obj_id, values)
> select
> obj_id,
> group_concat(value)
> from array
> group by obj_id;
Can't you materialize the "array" table shown earlier:
create table array (
obj_id integer,
seqnr integer,
value integer,
primary key (obj_id, seqnr))
> Unfortunately it does not work, because I need the array ordered by seqnr.
> I tried:
>
> insert into packed_array (obj_id, values)
> select obj_id, group_concat(value)
> from (
> select obj_id, value
> order by obj_id, seqnr
> )
> group by obj_id;
There should be a "from array" in there somewhere.
> But it did not seem to work right and I am not sure whether it should or not.
It is not guaranteed, but it'll probably work.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
