On 10 Oct 2011, at 12:37am, Frank Chang wrote: > Simon Slavin, Here is the schema which I used. CREATE TABLE > [BlobLastNameTest] ([FieldName] CHAR (25), [Vertices] BLOB )
Okay. That's not what you posted originally. Okay so we have CREATE TABLE BlobLastNameTest (FieldName TEXT PRIMARY KEY, Vertices BLOB) which means that the primary key is actually rowid, as normal. You also mention that you have CREATE INDEX sqlite_autoindex_BlobLastNameTest_1 ON BlobLastNameTest (Vertices) . That index may be helpful elsewhere but it's not useful for the enquiry you're asking about. You want to know > obtain the minumum rowid for each unique LastName I assume 'LastName' is what you call 'FieldName' here. In that case, the first thing you want is an index on FirstName. So do this: CREATE INDEX BLNTFieldName ON BlobLastNameTest (FieldName, rowid) One way to do it would be to do repeated SELECTs. I your software you keep a record of the last FieldName you found. You can start this variable off as the zero-length string ''. So your first SELECT would be SELECT rowid,FieldName FROM BlobLastNameTest WHERE FieldName>'' ORDER BY FieldName,rowid LIMIT 1 This will give you the entry in the table with the first FieldName. Suppose this FieldName is 'Absolom'. Then your next SELECT would be SELECT rowid,FieldName FROM BlobLastNameTest WHERE FieldName>'Absolom' ORDER BY FieldName,rowid LIMIT 1 And this will give you the entry with the next FieldName and the lowest rowid. You just keep doing this until your SELECT returns no results. Since you have declared an INDEX which is idea for this SELECT, SQLite should never need to do any scanning, each SELECT should lead it straight to one particular row using that index. I think you might also be able to do SELECT rowid,FieldName FROM BlobLastNameTest GROUP BY FieldName ORDER BY rowid You should test this to see if it works, and if it works sufficiently quickly. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
