"Marco Bambini" <ma...@sqlabs.net> wrote in message news:3265458b-af7b-434f-83e8-f9448bab0...@sqlabs.net > Hello all, > > I have a table foo (id INTEGER, tid INTEGER, optype INTEGER), > and I have some data into foo: > id id2 optype > --------------------- > 1 2 10 > 2 2 10 > 3 2 10 > 4 2 10 > 5 2 10 > 6 2 20 > 7 2 10 > 8 2 20 > 9 2 20 > 10 2 10 > > I need a query that returns results like: > 1,2,3,4,5 > 6 > 7 > 8,9 > 10 > > (divided by optype and sorted by id)
Try something like this: SELECT group_concat(id) FROM rsql_mvcc t1 WHERE transactionID=2 GROUP BY ( select min(id) from rsql_mvcc t2 where t2.transactionID=2 and t2.id <= t1.id and t2.optype=t1.optype and not exists ( select 1 from rsql_mvcc t3 where t3.transactionID=2 and t3.id > t2.id and t3.id < t1.id and t3.optype != t1.optype ) ); However, this is likely to be excrutiatingly slow ( O(N^3) ) for anything but small number of records. The problem doesn't lend itself easily to SQL. I submit it would likely be easier, and much faster, to run a query like this: select id, optype from rsql_mvcc WHERE transactionID=2 order by id; and assemble groups in your application code as you walk the resultset. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users