On 10/16/2012 4:56 PM, Bart Smissaert wrote:
Trying to make a query that can mark records, indicating them to
belong to a sequential group.
Giving the most simple example:
ID Value Group_Marker
---------------------------------------
1 D 1
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.
Something like this - but note that it's mostly an academic exercise. A
simple loop would work orders of magnitude faster than this statement.
update MyTable set Group_Marker = (
select count(*) from MyTable t1
where t1.ID <= MyTable.ID and t1.Value not in (
select t2.Value from MyTable t2 where t2.ID < t1.ID
order by t2.ID desc limit 1
)
);
In prose, for each record count the number of records below it
(inclusive) that are first-in-group; where first-in-group in turn is
defined as "a record such that the next record down by ID has a
different Value, or there is no smaller ID at all".
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
