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

Re: [sqlite] Permanently disabling AutoCommit

Op 24-mrt-2011, om 13:28 heeft Igor Tandetnik het volgende geschreven: > Jaco Breitenbach wrote: >> Is there an easy way to permanently disable the default AutoCommit >> using the >> C API? The nature of my application is such that I don't know >> exactly where >> a

Re: [sqlite] detect a flaw

Igor, Well, libversion is 3003006 Ok. Now I'll start looking for a reason I am using an older library... Thanks, Shalom On Thu, Mar 24, 2011 at 3:51 PM, Igor Tandetnik wrote: > Shalom Elkin wrote: > > I use 3.7.5 , and Linux (centos). > >

Re: [sqlite] detect a flaw

Shalom Elkin wrote: > I use 3.7.5 , and Linux (centos). Consider using sqlite3_libversion to confirm that. It's possible that more than one sqlite version is installed on the system, and you are linking to the wrong one. Your code looks OK (except that in "pragma

Re: [sqlite] "transaction" method of the Tcl bindings to SQLite

On 03/24/2011 07:23 PM, Richard Hipp wrote: > On Wed, Mar 23, 2011 at 7:05 AM, Jean-Marie CUAZ wrote: > >> Hello, >> >> In a Tcl script, is it safe to call the Tcl "_return_" command inside >> and before the end of a multi-statement "_transaction_" method (for >> aborting the

Re: [sqlite] detect a flaw

I use 3.7.5 , and Linux (centos). On Thu, Mar 24, 2011 at 12:56 PM, BareFeetWare wrote: > On 24/03/2011, at 4:41 PM, Shalom Elkin wrote: > > > Sorry - it doesn't work. > > sqlite3_exec with the pragma directive returns no error, but the

Re: [sqlite] Permanently disabling AutoCommit

Jaco Breitenbach wrote: > Is there an easy way to permanently disable the default AutoCommit using the > C API? The nature of my application is such that I don't know exactly where > a new transaction begins. Records are inserted into the database, and at > the end an

Re: [sqlite] "transaction" method of the Tcl bindings to SQLite

On Wed, Mar 23, 2011 at 7:05 AM, Jean-Marie CUAZ wrote: > Hello, > > In a Tcl script, is it safe to call the Tcl "_return_" command inside > and before the end of a multi-statement "_transaction_" method (for > aborting the Tcl procedure in case of Sql/application error) ? > >

[sqlite] Permanently disabling AutoCommit

Dear Experts, Is there an easy way to permanently disable the default AutoCommit using the C API? The nature of my application is such that I don't know exactly where a new transaction begins. Records are inserted into the database, and at the end an explicit COMMIT is requested. It seems that

Re: [sqlite] Creating a nested list

Erich93063 wrote: > theHtml += '' + areaRow.AreaName; > > tx.executeSql('SELECT * FROM Restaurants WHERE AreaID = ?;', > [areaRow.AreaID], > function(transaction, restaurantsResult) { > > theHtml = ''; Every time you enter the inner loop, you are wiping out previously

[sqlite] Creating a nested list

Hey guys, I'm not entirely sure this is the right forum for this, but I'm trying to figure out the best way to do this and I am using SQLite. I am cusing it for an Android app i am making. I'm using jQuery Mobile and PhoneGap, so we're dealing with JavaScript. I have created two tables in SQlite,

[sqlite] "transaction" method of the Tcl bindings to SQLite

Hello, In a Tcl script, is it safe to call the Tcl "_return_" command inside and before the end of a multi-statement "_transaction_" method (for aborting the Tcl procedure in case of Sql/application error) ? In other words is the transaction handled at the SQLite level "closed" safely (and

Re: [sqlite] detect a flaw

On 24/03/2011, at 4:41 PM, Shalom Elkin wrote: > Sorry - it doesn't work. > sqlite3_exec with the pragma directive returns no error, but the program > still agrees to insert a record that violates foreign_key constraint. What version of SQLite are you using? I think

Re: [sqlite] sqlite3_close( ) error

From: Simon Slavin To: General Discussion of SQLite Database Sent: Thu, March 24, 2011 5:39:58 AM Subject: Re: [sqlite] sqlite3_close( ) error On 24 Mar 2011, at 3:06am, Zaryab M. Munir wrote: >By the way,

Re: [sqlite] detect a flaw

I am ready to rebuild sqlite3 with the foreign_key support enabled as default. to do this, I should UNDEFINE SQLITE_OMIT_FOREIGN_KEY. I can't find a decent way to do this undef. I use g++ on linux. Thanks, Shalom On Thu, Mar 24, 2011 at 7:41 AM, Shalom Elkin wrote: >

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

> User also will probably execute some > queries that do some arithmetic operations on values in that column. > And they will be really surprised to see that not all the data is > numbers there. > > My test shows, that I can successfuly execute queries like (c is NUMERIC column): select c,