On Thu, Jun 23, 2011 at 8:37 AM, Jan Hudec <b...@ucw.cz> wrote: > 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? >
I thought sql shoudl do this itself, but other sql engines (mysql, mssql) also don't do order and group by 'correctly' so I've ended up either doing a temporary table or a self join to the first table with the group by to get the min/max of the other columns that I wanted to have ordered... > -- > Jan 'Bulb' Hudec <b...@ucw.cz> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
