John Elrick wrote: > I have a question for the SQLite experts here as to the most efficient > way to retrieve the following: > > Say we have a table: > > CREATE TABLE FOO ( > PARENT_ID INTEGER, > CHILD_ID INTEGER, > REVISION INTEGER > ); > > CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION); > > note that we could use > > CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION DESC); > > however, the current documentation implies that DESC does not improve > performance in the below. > > If we have the following data: > > INSERT INTO FOO VALUES (1,2,1); > INSERT INTO FOO VALUES (1,2,2); > INSERT INTO FOO VALUES (1,2,3); > > My goal is two fold. > > 1. Given a parent_id and a child_id, retrieve the last rowid > > select max(rowid) from foo where parent_id = :parentId and child_id = > :childId >
John, Do you mean the last rowid, or do you really mean the last revision? If you mean the latter then this should do it. select * from foo where parent_id = :parentId and child_id = :childId and revision = (select max(revision) from foo where parentId = :parentId and child_id = :childId) > 2. Given a parent_id, a child_id, and a revision, retrieve the last > rowid before that revision: > > select max(rowid) from foo where parent_id = :parentId and child_id = > :childId and revision < :revision > Similarly, for the second case. select * from foo where parent_id = :parentId and child_id = :childId and revision = (select max(revision) from foo where parentId = :parentId and child_id = :childId and revision < :revision) You need to be careful not to assume any particular ordering of the rows in the table. > My concern is that the queries above are suboptimal and will become a > performance impediment to the application. Can anyone recommend a more > efficient method of retrieving the data? > What makes you think these queries are sub-optimal? They will both use the index to find the correct revision number, and then use the index to find the matching row. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users