* Frank Chang: > This table could potentially hold 10 to 40 million rows. We are > using the following query to obtain the minumum rowid for each > unique LastName: > > sqlite> explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest > t1 > GROUP BY t1.FIELDNAME HAVING t1.rowid = (SELECT MIN(rowid) FROM > BlobLastNameTes > t where FieldName = t1.FIELDNAME);
You could try SELECT FieldName, rowid FROM BlobLastNameTest ORDER BY FieldName, rowid; and perform the aggregation in the application. Perhaps this is faster. A better query needs support for DISTINCT ON, which SQLite lacks AFAIK. But speed will obviously be limited because you cannot avoid traversing the index for the whole table. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
