Frank Chang <[email protected]> wrote:
> Igor Tandetnik,
> The fieldname groups in our BlobLastNameTable consist of
> multiple rows where each pair of columns [FieldName,
> BLOB[Vertices]] is unique.
How so? You have FieldName declared as PRIMARY KEY. From your original post:
CREATE TABLE [BlobLastNameTest] ([FieldName] CHAR (25) PRIMARY KEY, [Vertices]
BLOB )
PRIMARY KEY means there can only be one row with any given value of FieldName.
If you have since changed your schema, then post the new CREATE TABLE statement
for BlobLastNameTest table, and any CREATE INDEX statements related to it.
> Therefore, every fieldname group does not just have a single row but instead
> 1000's or
> 10000's rows. So that is why we use a group by/order by and
> subselect clause to locate the first/minimum row id row
> in each fieldname group.
Assuming this is true, what's wrong with
select FieldName, min(rowid) from BlobLastNameTest group by FieldName;
> Once we know the first/minimum row id of each unique fieldname group, we
> would lke to write a
> sqlite UPDATE Statement to accumulate all the BLOB vertices of all the rows
> with that unique fieldname into the first(i.e
> MIN(ROWID)) row's BLOB(Vertices)column for each unique fieldname group.
What do you mean by "accumulate"? Concatenate? I don't think you can do that
with SQL alone - you'll have to write some code. Personally, I'd do something
like this (in pseudocode):
stmt = prepare("select FieldName, rowid, Vertices from BlobLastNameTest order
by FieldName, rowid")
currentFieldName = "";
firstRowId = -1;
blob = ""
while (stmt.Step) {
if (currentFieldName != stmt.FieldName) {
commitBlob(firstRowId, blob)
currentFieldName = stmt.FieldName
firstRowId = stmt.rowid
blob = ""
}
blob += stmt.Vertices // whatever you mean by "accumulate", do it here
}
commitBlob(firstRowId, blob)
function commitBlob(rowid, blob) {
if (rowid > 0) {
execute "update BlobLastNameTest set Vertices = ? where rowid = ?;"
with parameters (blob, rowid)
}
}
> Then we would like to discard all the rows in each
> fieldname group of rows that have an rowid different from the first row
That one's easy:
delete from BlobLastNameTest where rowid !=
(select min(rowid) from BlobLastNameTest t2
where t2.FieldName = BlobLastNameTest.FieldName);
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
