Re: [sqlite] how to do this query?

On 3/24/2011 4:47 PM, Bart Smissaert wrote: >> Deletes all but the most recent entry for each patient. Is this what you > are after? > > Yes, that is exactly it. > > delete from xxx where entry_id not in ( > select entry_id from xxx where xxx.patient_id = patient_id > order by start_date desc

Re: [sqlite] how to do this query?

> Deletes all but the most recent entry for each patient. Is this what you are after? Yes, that is exactly it. delete from xxx where entry_id not in ( select entry_id from xxx where xxx.patient_id = patient_id order by start_date desc limit 1); This deletes all but one record. It should be a

Re: [sqlite] how to do this query?

On 3/24/2011 3:32 PM, Bart Smissaert wrote: > Couldn't get this to work yet. > What would be the full SQL, including the order by clause? delete from xxx where entry_id not in ( select entry_id from xxx where xxx.patient_id = patient_id order by start_date desc limit 1); Deletes all but

Re: [sqlite] how to do this query?

Couldn't get this to work yet. What would be the full SQL, including the order by clause? RBS On Thu, Mar 24, 2011 at 7:13 PM, Igor Tandetnik wrote: > On 3/24/2011 3:00 PM, Bart Smissaert wrote: >> SQLite objects against this SQL, particularly the first t1 after xxx >> >>

Re: [sqlite] how to do this query?

Thanks, that works indeed nicely. RBS On Thu, Mar 24, 2011 at 7:12 PM, Jim Morris wrote: > A simple restatement should work: > > delete > from xxx > where entry_id in (select > t1.entry_id > from > xxx t1 > where not > t1.entry_id in(select > t2.entry_id > from > xxx t2

Re: [sqlite] how to do this query?

On 3/24/2011 3:00 PM, Bart Smissaert wrote: > SQLite objects against this SQL, particularly the first t1 after xxx > > delete > from > xxx t1 > where not > t1.entry_id in(select > t2.entry_id > from > xxx t2 > where > t1.patient_id = t2.patient_id > order by > t2.start_date desc limit 1) delete

Re: [sqlite] how to do this query?

A simple restatement should work: delete from xxx where entry_id in (select t1.entry_id from xxx t1 where not t1.entry_id in(select t2.entry_id from xxx t2 where t1.patient_id = t2.patient_id order by t2.start_date desc limit 1)) On 3/24/2011 12:00 PM, Bart Smissaert wrote: > delete > from > xxx

[sqlite] how to do this query?

SQLite objects against this SQL, particularly the first t1 after xxx delete from xxx t1 where not t1.entry_id in(select t2.entry_id from xxx t2 where t1.patient_id = t2.patient_id order by t2.start_date desc limit 1) How could I achieve this with a different syntax? RBS