Yes, thanks, that has the same speed as the second query and it looks neater. Has the similar query plan as the second query:
order from detail --------------------------------------- 0 0 TABLE A3Morb5B4_F USING PRIMARY KEY 0 0 TABLE A3Morb5B4_F WITH INDEX IDX_A3Morb5B4_E_PATIENT_ID ORDER BY RBS On Thu, Mar 4, 2010 at 11:32 AM, Simon Davies <simon.james.dav...@googlemail.com> wrote: > On 4 March 2010 10:54, Bart Smissaert <bart.smissa...@gmail.com> wrote: >> One table with this create SQL: >> >> CREATE TABLE Table1([PATIENT_ID] INTEGER, >> [ENTRY_ID] INTEGER PRIMARY KEY, >> [READ_CODE] TEXT, >> [ADDED_DATE] TEXT, >> [START_DATE] TEXT) >> >> and a non-uique index on PATIENT_ID >> >> Then the following 2 queries, that give the same result: >> >> select * >> from >> Table1 t1 >> where >> t1.entry_id in(select >> max(t2.entry_id) >> from >> Table1 t2 >> where >> t1.patient_id = t2.patient_id) >> order by >> patient_id asc >> >> select * >> from >> Table1 >> where >> rowid in(select >> t1.rowid >> from >> Table1 t1 >> where >> t1.entry_id = (select >> max(t2.entry_id) >> from >> Table1 t2 >> where >> t1.patient_id = t2.patient_id)) >> order by >> patient_id asc >> >> Now the second query is about twice as fast and looking at the query >> plans it seems that it because the second query uses >> the primary key and the first doesn't: >> >> first query: >> order from detail >> --------------------------------------- >> 0 0 TABLE A3Morb41C_F AS t1 WITH INDEX >> IDX_A3Morb41C_E_PATIENT_ID ORDER BY >> 0 0 TABLE A3Morb41C_F AS t2 WITH INDEX >> IDX_A3Morb41C_E_PATIENT_ID ORDER BY >> >> second query: >> order from detail >> --------------------------------------- >> 0 0 TABLE A3Morb41C_F USING PRIMARY KEY >> 0 0 TABLE A3Morb41C_F AS t1 >> 0 0 TABLE A3Morb41C_F AS t2 WITH INDEX >> IDX_A3Morb41C_E_PATIENT_ID ORDER BY >> >> >> Is there a way to force the use of the primary key in the first query >> or is there any other way to tackle this? > > Perhaps this does what you want: > > select * from Table1 > where entry_id in ( > select max( entry_id ) from Table1 group by patient_id > ); > >> >> >> RBS >> > > Regards, > Simon > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
