Hello All,
Is there a way to do group by query with well defined order of calls to the
aggregate function?
The problem is as follows. I have some objects in a database identified with
"obj_id". For each object, there are some arrays, represented in the
normalized form like:
create table array (
obj_id integer,
seqnr integer,
value integer,
primary key (obj_id, seqnr))
For each object there are *several* arrays like this (and they are actually
queries/view, not simple tables).
Now I need to read all the objects, one at a time, with all the arrays
relating to it, and process it with in the application. Instead of running
several
select value from array where obj_id = :id order by seqnr
for each object, which would take quite a lot of code and tends to perform
poorly especially since there is non-trivial join in place of "array". 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;
and than join these tables. This is faster because the aggregating selects
read fewer tables individually and the final join is fast, because it's
joining by integer primary keys. It is also easier, because it does not need
any stateful reading code.
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;
But it did not seem to work right and I am not sure whether it should or not.
Is there any way to do this in SQL, or will I have to aggregate it in code?
--
Jan 'Bulb' Hudec <b...@ucw.cz>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
