"Marco Bambini" <[email protected]> wrote
in message news:[email protected]
> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
