Re: [sqlite] Selecting last item in a unique index

2008-02-15 Thread John Elrick
Dennis Cote wrote: > John Elrick wrote: > >> Last rowid. Rowid will be a key linking to other tables. >> >> > > What table will rowid be linked to? Are you linking children rows back > to parent rows? > > Not exactly. This is a theoretical implementation of WinFS modified to our

Re: [sqlite] Selecting last item in a unique index

2008-02-15 Thread Dennis Cote
John Elrick wrote: > > Last rowid. Rowid will be a key linking to other tables. > What table will rowid be linked to? Are you linking children rows back to parent rows? You should generally use an excplict column as a foreign key (i.e. for linking) to other tables. You can use SQLite's

Re: [sqlite] Selecting last item in a unique index

2008-02-15 Thread Dennis Cote
Ken wrote: > select * from foo > where rowid = > (select rowid from foo > where parent_id = 1 > and child_id = 2 > group by parent_id, child_id > having revision = max(revision)) > > sqlite> explain query plan >...> select * from foo >...> where

Re: [sqlite] Selecting last item in a unique index

2008-02-15 Thread John Elrick
Dennis Cote wrote: > John Elrick wrote: > >> I have a question for the SQLite experts here as to the most efficient >> way to retrieve the following: >> >> SNIP > John, > > Do you mean the last rowid, or do you really mean the last revision? > > Last rowid. Rowid will be a key

Re: [sqlite] Selecting last item in a unique index

2008-02-15 Thread Ken
select * from foo where rowid = (select rowid from foo where parent_id = 1 and child_id = 2 group by parent_id, child_id having revision = max(revision)) sqlite> explain query plan ...> select * from foo ...> where rowid = ...> (select

Re: [sqlite] Selecting last item in a unique index

2008-02-15 Thread Dennis Cote
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

[sqlite] Selecting last item in a unique index

2008-02-15 Thread John Elrick
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