Re: [sqlite] Sqlite Concurrent access issue

You're probably seeing disk thrashing. Try increasing your database cache size: .pragma cache_size XX It's in kilobytes. Try and make it as big as your database if you can. In other words, cache the whole thing. Secondly, you may want to try using the FTS3/FTS4 search capability if

[sqlite] SQL help

Hi I have a large table with some duplicate rows that I want to delete. Essentially I have two columns, one containing a date and one containing a number. The number column can contain duplicates. For any row containing duplicate values I want to remove all rows bar the oldest. i.e. if the data

Re: [sqlite] SQL help

On Dec 22, 2011, at 4:08 PM, Paul Sanderson wrote: > I have a large table with some duplicate rows that I want to > delete. Something along these lines: delete fromfoo where rowid not in ( selectmax( rowid ) from foo group by bar,

Re: [sqlite] SQL help

On 22 December 2011 15:08, Paul Sanderson wrote: > Hi I have a large table with some duplicate rows that I want to > delete. Essentially I have two columns, one containing a date and one > containing a number. The number column can contain duplicates. For any > row

Re: [sqlite] SQL help

On 12/22/2011 11:00 AM, Simon Davies wrote: DELETE FROM t WHERE rowid NOT IN ( SELECT rowid FROM ( SELECT rowid, num, date FROM t ) CROSS JOIN ( SELECT num min_num, min( date ) min_date FROM t GROUP BY num ) ON num=min_num AND date=min_date ); Seems a bit complicated. How about

Re: [sqlite] Parameters are not allowed in views

Where can I learn more about "restrict it". I'm not familiar with the syntax for using the question mark. Is there a specific part of the documentation that explains it and how it works? Thanks, dvn On Wed, Dec 21, 2011 at 5:00 PM, Petite Abeille wrote: > > On Dec

Re: [sqlite] Sqlite Concurrent access issue

On Thu, 22 Dec 2011 13:49:48 +, "Black, Michael (IS)" wrote: > You're probably seeing disk thrashing. > > Try increasing your database cache size: > > .pragma cache_size XX Make that PRAGMA cache_size=xx; > It's in kilobytes. Almost correct.

Re: [sqlite] SQL help

At 18:09 22/12/2011, you wrote: DELETE FROM t WHERE rowid != (SELECT t2.rowid FROM t t2 where t2.num = t.num ORDER BY t2.date LIMIT 1); Are you sure that date will collate correctly, as entries appear to be in dd/mm/ format? Dates in non_ISO format are always a pain. IMHO if dates are

[sqlite] Storing dates/times (was: SQL help)

On 22 Dec 2011, at 9:44pm, Jean-Christophe Deschamps wrote: > IMHO if dates are to be stored in string format, then one should always store > them in /MM/DD (with leading zeroes) then eventually display dates in > whatever format suits users at the application level. To save you time in

Re: [sqlite] Storing dates/times (was: SQL help)

On Thu, Dec 22, 2011 at 5:25 PM, Simon Slavin wrote: > > On 22 Dec 2011, at 9:44pm, Jean-Christophe Deschamps wrote: > > > IMHO if dates are to be stored in string format, then one should always > store them in /MM/DD (with leading zeroes) then eventually display >

Re: [sqlite] Storing dates/times (was: SQL help)

On 22 Dec 2011, at 10:29pm, Richard Hipp wrote: > http://en.wikipedia.org/wiki/ISO_8601 Wow. Yes, that's where I got the 'T' from. But I seem to have forgotten or ignored everything else from it. Possibly back in the days when storing those extra slashes would have cost me or my customers

Re: [sqlite] : about add sqlite database cloumn

I have a question about C binding for sqlite, I have a table like this: NamePrice1 Price2 Sum A1 23 231 A2 22 12 A3 21 223 how to use functin int myfunc() { int tt=0; if (price1 >2) tt++; if (price2>1) tt++; if (price2>12) tt++; ...