On 23 Nov 2012, at 9:01am, Steffen Mangold <[email protected]> wrote:
> But it should be: > ID | Begin | End | Status > ------------------------------------------------------------------------------- > 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:05 | status1 > 0 | 2012-07-24 22:23:10 | 2012-07-24 22:23:16 | status2 > 1 | 2012-07-24 22:23:21 | 2012-07-24 22:23:26 | status1 > 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 You can find the 'Begin's by, for every line, looking up the previous line with a sub-SELECT and checking to see whether it has the same status. Then you do the 'Ends' by looking up the following line. Might have some trouble with the first and last lines in the table. It would be a horribly complicated SQL command. However, do you absolutely have to do this inside a SQL command ? It's trivial in any programming language but puts a great load on a SQL engine. In code you only have to read every row once, in an order already provided by an INDEX. If you do it in SQL you also have to do at least one look-up for every row, keyed on a long string. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
