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
