Re: [sqlite] EXT : find sequential groups

[Bart Smissaert]

Yes, that should be 4 groups, marked with 1, 2, 3 and 4.

RBS


On Tue, Oct 16, 2012 at 11:15 PM, Igor Tandetnik  wrote:
> On 10/16/2012 6:08 PM, Black, Michael (IS) wrote:
>>
>> Do this work for you?
>>
>> CREATE TABLE Groups (Value);
>> insert into Groups select distinct(Value) from test;
>
>
> Since the OP mentioned "sequential groups", I assumed that a sequence like
> "A,A,B,B,A,A,B,B" should count as four groups, not two as your approach
> would end up with.
> --
> Igor Tandetnik
>
>
> ___
> 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

Re: [sqlite] EXT : find sequential groups

[Igor Tandetnik]

On 10/16/2012 6:08 PM, Black, Michael (IS) wrote:

Do this work for you?

CREATE TABLE Groups (Value);
insert into Groups select distinct(Value) from test;


Since the OP mentioned "sequential groups", I assumed that a sequence 
like "A,A,B,B,A,A,B,B" should count as four groups, not two as your 
approach would end up with.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT : find sequential groups

[Black, Michael (IS)]

Do this work for you?

CREATE TABLE Test(ID,Value,Group_Marker);
INSERT INTO "Test" VALUES(1,'D',0);
INSERT INTO "Test" VALUES(2,'X',0);
INSERT INTO "Test" VALUES(3,'X',0);
INSERT INTO "Test" VALUES(4,'X',0);
INSERT INTO "Test" VALUES(5,'A',0);
INSERT INTO "Test" VALUES(6,'B',0);
SELECT * FROM Test;
CREATE TABLE Groups (Value);
insert into Groups select distinct(Value) from test;
SELECT * FROM Groups;
update test set Group_Marker=(select rowid from Groups where 
Groups.Value=test.Value);
SELECT * FROM Test;

sqlite> CREATE TABLE Test(ID,Value,Group_Marker);
sqlite> INSERT INTO "Test" VALUES(1,'D',0);
sqlite> INSERT INTO "Test" VALUES(2,'X',0);
sqlite> INSERT INTO "Test" VALUES(3,'X',0);
sqlite> INSERT INTO "Test" VALUES(4,'X',0);
sqlite> INSERT INTO "Test" VALUES(5,'A',0);
sqlite> INSERT INTO "Test" VALUES(6,'B',0);
sqlite> SELECT * FROM Test;
1|D|0
2|X|0
3|X|0
4|X|0
5|A|0
6|B|0
sqlite> CREATE TABLE Groups (Value);
sqlite> insert into Groups select distinct(Value) from test;
sqlite> SELECT * FROM Groups;
D
X
A
B
sqlite> update test set Group_Marker=(select rowid from Groups where Groups.Val
e=test.Value);
sqlite> SELECT * FROM Test;
1|D|1
2|X|2
3|X|2
4|X|2
5|A|3
6|B|4

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Tuesday, October 16, 2012 3:56 PM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] find sequential groups

Trying to make a query that can mark records, indicating them to
belong to a sequential group.
Giving the most simple example:

IDValue   Group_Marker
---
1  D1
2  X 2
3  X 2
4  X 2
5  A 3
6  B 4

Given I have a table with data in the fields ID and Value, but not in
Group_Marker, can I make a SQL
that will find the values in the field Group_Marker as above and
update that field to hold those
values. The field Value holds the data indicating a sequential group,
so record 2, 3 and 4 are
the second group, hence I need the 2 in the field Group_Marker. ID is
the field indicating the sequence.
This is easy to do in code with a simple loop, but not sure now how to
do it in SQL.

RBS
___
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