Re: [sqlite] Mixed ASC and DESC in single column

On 11/21/2019 8:08 AM, Hamish Allan wrote: > Thank you very much Clemens, but I've realised I've asked the wrong > question. > > Is it possible to achieve the same if the identifiers are not unique? > > So for instance: > > CREATE TABLE IF NOT EXISTS Data (identifier TEXT, info TEXT); > > INSERT

Re: [sqlite] Question about: Adding a record to a table with select failure

Not sure this helps, a way to a conditionally insert based on if record already exists, is a select with literals left outer joined to the maybe record and use a where test value is null. Something like this pseudo SQL insert into T (valueA, valueB') (select 'ValueA', 'ValueB' left outer join T

Re: [sqlite] How to use CASE statement to SUM() some numbers

Maybe something like: CREATE VIEW "Sum of Expenses Between two Dates" AS SELECT Date, sum( CASE WHEN Date BETWEEN date('now', '-1 months') AND date('2019-11-04', '-1 days') THEN Expense ELSE 0 END) as 'Sum of Expenses:' FROM Expenses; On 11/15/2019 12:22 PM, David

Re: [sqlite] Count error?

Using a sub-select should work select (select count(n) from t0) as "t0_count", (select count(n) from t1) as "t1_count", (select count(n) from t2) as "t2_count" ; On 11/1/2019 9:07 AM, Jose Isaias Cabrera wrote: > Jose Isaias Cabrera, on Friday, November 1, 2019 11:51 AM, wrote... >> >>

Re: [sqlite] Last record

In your application you can create a wrapping iterator that pre-reads the next value instead or directly accessing the low level step function. Then you can ask if it is the last. On 10/15/2019 9:44 AM, Simon Slavin wrote: > On 15 Oct 2019, at 5:38pm, Philippe RIO <51...@protonmail.ch> wrote: >

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

What is the journal mode? On 3/12/2019 10:30 AM, Ted Goldblatt wrote: On Tue, Mar 12, 2019 at 11:45 AM James K. Lowden wrote: On Tue, 12 Mar 2019 10:36:37 -0400 ted.goldbl...@gmail.com wrote: The problem is basically that as part of a test, the customer wants to power fail the device, and

Re: [sqlite] Can this be done with SQLite

Wouldn't the mod operator do this? Do an update and set key = 1 + (5 + key)%5 On 1/22/2018 12:38 PM, David Raymond wrote: Unless I'm reading you wrong then just do the normal begin transaction; update playYouTubeVideo set speed = ( select speed from playYouTubeVideo where key = '2')

Re: [sqlite] Binding an order by

What you may be able to do is to use a case statement(s) which uses a bound variable to either a column or dummy E.g order by case orderControlValue = 1 then column1 else "" end, ... On 10/5/2017 11:51 AM, Igor Tandetnik wrote: On 10/5/2017 2:45 PM, Stephen Chrzanowski wrote: Given the

[sqlite] Incremental backup/sync facility?

Doesn't this eliminate the use of prepared statements? On 5/6/2016 11:10 AM, Jeffrey Mattox wrote: > As an aside, this is how Apple syncs Core Data to iCloud (and then to > multiple iOS devices) if the backing store uses SQLite (the default). When a > small amount of data changes (which is

[sqlite] Parsing the contents of a field

Might be doable with a recursive CTE On 1/13/2016 1:22 AM, Bart Smissaert wrote: > It probably can be done with just SQLite's built-in text functions such as > instr and substr, > although with 20 to 30 items it may get a bit messy and complex. > > RBS > > On Wed, Jan 13, 2016 at 5:42 AM, audio

[sqlite] MultiThread Error 10 on Windows Mobile 6.x

ype = 1//SHARED_LOCK lastErrno = 6//ERROR_INVALID_HANDLE Thoughts? On 11/3/2015 6:12 PM, Jim Morris wrote: > I have a connection used by the main (UI) thread and recently added a > background thread, with own connection(no shared cache) to upload > changes to server on Windows Mo

[sqlite] MultiThread Error 10 on Windows Mobile 6.x

No. The app works, however when I scroll the list there are may requests to get records from the DB and it seems to interact with the background thread to produce this error. On 11/3/2015 7:02 PM, Joe Mistachkin wrote: > Jim Morris wrote: >> Is there a graceful wo

[sqlite] MultiThread Error 10 on Windows Mobile 6.x

I have a connection used by the main (UI) thread and recently added a background thread, with own connection(no shared cache) to upload changes to server on Windows Mobile 6.0/6.5 devices and upgraded to SQLite 3.9.2. The background thread is getting an error 10, extended error 3850 as a

[sqlite] Moving from Sqlite 3.5.9 to 3.9.1

First, I was able to drop in the 3.9.2 sqlite.c and sqlite.h files, build and run the app without compiler, linker or other apparent errors. Great job by dev team! Our Windows Mobile 6.x app has been using SQLite 3.5.9, threading mode=1. I added a background thread, with its own connection

[sqlite] When was PRAGMA busy_timeout added to SQLite3

I'm using an archaic version, I know, but busy_timeout doesn't seem to work. It doesn't return the value I set. When was it added? sqlite3 temp.db SQLite version 3.5.9 Enter ".help" for instructions sqlite> PRAGMA busy_timeout=3; sqlite> PRAGMA busy_timeout; sqlite>

[sqlite] When was PRAGMA busy_timeout added to SQLite3

I'm using an archaic version, I know, but busy_timeout doesn't seem to work. It doesn't return the value I set. When was it added? sqlite3 temp.db SQLite version 3.5.9 Enter ".help" for instructions sqlite> PRAGMA busy_timeout=3; sqlite> PRAGMA busy_timeout; sqlite>

[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?

If you are not using WAL mode that might help. On 7/2/2015 7:59 AM, Kathleen Alexander wrote: > Hi Adam, > > Thank you very much for your response. I had not considered using copies of > the database. In my method there are cases where writes to the database by > one process may be relevant to

[sqlite] Transpose selected rows into columns

You might try select SerialNumber, V0, V5, V5-V0 from (select SerialNumber, max(case Stim when 'V0' then Resp else null end) V0, max(case Stim when 'V5' then Resp else null end) V5 from MyTable group by SerialNumber) On 4/7/2015 6:58 AM, Drago, William @ CSG - NARDA-MITEQ wrote: >

Re: [sqlite] WITHOUT ROWID option

To improve efficiency you could add "where 1=2" to avoid returning any rows. Should just check validity. On 5/7/2014 8:19 AM, Stephan Beal wrote: On Wed, May 7, 2014 at 4:57 PM, Simon Slavin wrote: somehow ? Perhaps the ROWID field of a table might have its own

Re: [sqlite] inner vs. outer join inconsistency

I believe a check constraint with an appropriate typeof comparison has been suggested for this usage. On 3/6/2013 6:29 AM, Ryan Johnson wrote: I would agree that no warning is needed for for columns that don't state any affinity, or for a non-affinity FK that refers to some PK with affinity.

Re: [sqlite] JDBC Drivers for SQLite?

http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers Check out Google for more. On 12/12/2012 8:54 AM, Tilsley, Jerry M. wrote: All, Might be a silly question, but does anyone know if any JDBC drivers exist for SQLite? Thanks, Jerry

Re: [sqlite] Ordering of fields in a join

Adding the warning to the explain plan output should work well. ... And yet the coding mistake in the SQL query was very subtle. It makes me wonder if we shouldn't somehow come up with a "warning" mechanism in SQLite to give developers a heads-up on error-prone constructs, such as using ==

Re: [sqlite] classic update join question

This analysis is a good candidate for inclusion in a FAQ or similar document. On 9/5/2012 7:28 PM, Keith Medcalf wrote: sqlite> create table alpha (frequency, term); sqlite> create table beta (term, frequency); sqlite> create index betaterm on beta(term); sqlite> .explain sqlite> explain query

Re: [sqlite] SQLite4 (don't scream)

Are there significant improvements in speed for existing SQL? How does the compiled size compare with SQLite3? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble importing hex encoded blob

/** ** This file is an amalgamation of many separate C source files from SQLite ** version 3.7.12. By combining all the individual C code files into this $ grep blobBuffer * $ On Mon, May 14, 2012 at 8:24 AM, Jim Morris<j

Re: [sqlite] Trouble importing hex encoded blob

We added blob import on an old version of the shell, 3.5.9. Using a simple HexToByte function. To function: static int do_meta_command(char *zLine, struct callback_data *p){ Added:unsigned char * blobBuffer = NULL; In the loop // * Bind cached values to prepared statement.

Re: [sqlite] Data Import Techniques

If you are not wrapping the inserts in an explicit transaction, try that. On 5/2/2012 9:04 AM, Nigel Verity wrote: Hi I am writing an application which requires approximately 50,000 items to be imported from a text file into a table. Each item is a single string of 8 characters, and the

Re: [sqlite] Re Query planner creating a slow plan

It is possible using an alias would force better behavior: selsect theDate from (select transfer_date as theDate from transfer_history where regn_no='039540' and transfer_date<= '2012-05-01') order by theDate ___ sqlite-users mailing list

Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

it on the scan gun? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jim Morris Sent: Thursday, February 09, 2012 12:22 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5 It will definitely

Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

Don't know about those devices but we use the MC55A and related device without issue with C++ and I'm pretty sure they support C#. http://www.motorola.com/Business/US-EN/Business+Product+and+Services/Mobile+Computers/Handheld+Computers/MC55A0 On 2/9/2012 9:24 AM, Richard Hipp wrote: On Thu,

Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

It will definitely run. Pelles is new to me. Our application uses SQLite 3.5.9 on WinCE 4.2 through 6.5 using C++. I don't recall that we had to change any source, but we did modify the shell for better input handling, but there were probably some compiler flags we needed to set. Been

Re: [sqlite] General question on sqlite3_prepare, the bind and resets of prepared statements

Yes, works great! On 11/11/2011 4:24 PM, Matt Young wrote: Embedded Sqlite3 questions: I want to load and prepare multiple statements, keep them prepared and when I want to use one of them, I will reset, bind and step. Can pre-prepare multiple independent statements, then run them one at a

Re: [sqlite] ensuring uniqueness of tuples spanning across multipletables?

On 10/6/2011 10:43 PM, Ivan Shmakov wrote: Jim Morris writes: > The recent thread may relate: "[sqlite] Is there an efficient way to > insert unique rows (UNIQUE(a, b, c)) into an fts3 virtual table?" > INSERT INTO fts3_table (a,b,c) > SELECT 'an A',

Re: [sqlite] ensuring uniqueness of tuples spanning across multipletables?

The recent thread may relate: "[sqlite] Is there an efficient way to insert unique rows (UNIQUE(a, b, c)) into an fts3 virtual table?" INSERT INTO fts3_table (a,b,c) SELECT 'an A','a B','a C' WHERE NOT EXISTS (SELECT DISTINCT a,b,c FROM fts3_table WHERE a='an A' AND b='a B' AND c='a C'); The

Re: [sqlite] How to get the grand total of count(*) in a select statement

That should be select 'Total', count(*) from hosts On 10/3/2011 11:52 AM, Jim Morris wrote: This should do it. select region, count(*) from hosts group by region union all select 'Total, count(*) from hosts ; On 10/3/2011 11:49 AM, James Kang wrote: select region, count(*) from hosts group

Re: [sqlite] How to get the grand total of count(*) in a select statement

This should do it. select region, count(*) from hosts group by region union all select 'Total, count(*) from hosts ; On 10/3/2011 11:49 AM, James Kang wrote: select region, count(*) from hosts group by region ___ sqlite-users mailing list

Re: [sqlite] LEFT JOIN optimization

Your where clause "WHERE ItemsME.IDR ..." is only satisfied if there is an associated ItemsME record so the left outer join is pointless. Just use the inner join. Normally the left outer join would include all of ItemsME_Properties, that probably explains the table scan.

Re: [sqlite] PHP, SQLite3 object API, SQLite3::escapeString

I'd guess it was for escaping strings used to build SQL statements by concatenation rather than using prepared statements and binding. On 8/18/2011 7:12 AM, Simon Slavin wrote: > ... > The SQLite3 object API for PHP includes a function SQLite3::escapeString . > The documentation for it doesn't

Re: [sqlite] aggregate by break in sequence

You can't replace multiple rows in a single insert/update/delete statement. You might consider copying the duplicates to a temp table, delete them from the old then use a select on the temp table to generate the new rows for the old table. The select portion would be something like select

Re: [sqlite] "override" table?

A union variation that is more amenable to generic selects: select a,b,c,1 as tablename from real where id=123 and not exists (Select 1 from over where over.id = real.id) union select a,b,c,2 as tablename from over where id=123 ___ sqlite-users

Re: [sqlite] System.Data.SQLite: commands not persisting on db.

On 7/15/2011 1:26 AM, Mattia wrote: > - deleting the old database (data.db) and renaming new.db with the > correct name (new.db becomes data.db). After this step, as a test, reopen the connection to new.db and ensure that the data is there. Are you sure a commit is done before closing

Re: [sqlite] INSERT OR UPDATE?

Or do an update and if no records are modified then do an insert. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

The between operator is order dependent. This variation might work: SELECT d from T_d inner join (select min(pos) as xMin, max(pos) as yMax FROM T_x WHERE txt = '1990' OR txt='1991') as xcriteria on xPos between xMin and xMax inner join (select min(pos) as yMin, max(pos) as yMax FROM T_y WHERE

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

string literals are enclose in single quotes not double quotes" select (select v from t1 where n='a') wrong,* from a1; On 6/28/2011 11:42 AM, thilo wrote: > select (select v from t1 where n="a") wrong,* from a1; ___ sqlite-users mailing list

Re: [sqlite] Query with UNION on large table

Did you try to time a simpler select: SELECT min(n1, n2) as new1, max(n1,n2) as new2 FROM table1 group by new1, new2 having count(*)> 1 ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLITE run slow

You might also consider a trigger to calculate the distance once on insert/update. They you could use an index. On 6/14/2011 4:53 AM, Simon Slavin wrote: > On 14 Jun 2011, at 12:11pm, Martin.Engelschalk wrote: > >> make sure you have an index on category and distance, like >> >> create index

Re: [sqlite] HELP: SQLException getErrorCode vs gerErrorMessage()

I've only used the xerial driver recently and haven't checked for the error code on exception. It does come with source, so you may be able to walk into the code to see how the value is set/retrieved. ___ sqlite-users mailing list

Re: [sqlite] Create DB file and then Create Table - Table FAILS.

This line seems erroneous: SQLiteCommand sqlCmd(%conn); Isn't the percent the modulus operator? Shouldn't it be: SQLiteCommand sqlCmd(); On 6/1/2011 5:06 PM, Don Ireland wrote: > I'm hoping someone can help me with this. > > Using Visual Studio C++, the following code DOES create the DB file. >

Re: [sqlite] Better way to get records by IDs

If you just need them in descending order, i.e. not an arbitrary order, then "order by rec desc" will work. On 5/20/2011 7:23 AM, jose isaias cabrera wrote: > "Martin Engelschalk" on Friday, May 20, 2011 10:21 AM wrote... > > >> Hi, >> >> to order, you have to use "order by". In that case,

Re: [sqlite] Query efficiency

You must use the alias if specified: select ar.* from aa ar, ab ab1, ab ab2; rather than select aa.* from aa ar, ab ab1, ab ab2; On 5/19/2011 10:33 AM, Matthew Jones wrote: > select aa.* from aa ar, ab ab1, ab ab2; ___ sqlite-users mailing list

Re: [sqlite] Problem with sqlite3_prepare_v2?

Yes, transaction are designed to work with multiple statements. Begin Statement 1 Statement 2 ... Statement N Commit On 5/17/2011 7:58 AM, StyveA wrote: > Hello again, > > I've got an other question about prepare : > > Is it possible to make a BEGIN transaction, then prepare many statements >

Re: [sqlite] Best JDBC driver for SQLite?

I've just started using Xerial also for a small project and have not had any problems so far. I haven't used any other JDBC wrappers so have no comparison info. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLite3.DLL 3.7.6 memory leaks

Are you sure these leaks aren't yours? Although I don't know the Sqlite internals some of the data values don't seem to be related to Sqlite, like: c:/DEV/Platform/ PolicyDataPack.i You can try to use the allocation number to narrow down the code location. Don't remember the specifics though.

Re: [sqlite] How to Use an Apostrophe in a Text Field?

Did you try doubling the apostrophes? *Goin'' Down the Road Feelin'' Bad* On 4/17/2011 6:16 PM, Simon Slavin wrote: > On 17 Apr 2011, at 11:54pm, Alan Holbrook wrote: > >> I'm using SQLite with VBE2008. I've defined a table with a number of text >> fields in it. If the information I want to

Re: [sqlite] GROUP BY Problem

Did you want to use correlated sub queries? Something like: SELECT c1,c2, (select sum(t2.c3) FROM t2 WHERE t2.key2=t1.key1) as mySum, (select count(t3.c4) FROM t3 where t3.key3=t1.key1) as myCount FROM t1 ; On 4/7/2011 5:31 PM, Pete wrote: > I am trying to use GROUP BY to summarise information

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

Re: [sqlite] .import FILE TABLE

.separator "," .import myPath/myCSVfile myTable We use a "import" file with these commands. On 3/10/2011 2:32 PM, jcilibe...@comcast.net wrote: > Thanks, but doesn't seem to work: > > > I used command> > - Original Message - > From: "Gerry Snyder" > To: "General

Re: [sqlite] .import FILE TABLE

Make sure your separator is the "," http://www.sqlite.org/sqlite.html On 3/10/2011 12:28 PM, jcilibe...@comcast.net wrote: > Hello, > > > Unbelievably active user group! > > > I have been unable to import a CSV text file from MS Access to sqlite: > 1. Created a small table (3 fields and 1 record)

Re: [sqlite] Help with join

A correlated sub-query might work for you. SELECT [Analyzers].[AnalyzerID] , [Analyzers].[Name] AS [Analyzer] , [Analysis].[AnalysisID] , [Analysis].[ScanID] , [Analysis].[Timestamp] , [Analysis].[EndTime] , (SELECT COUNT(*) AS NumDefects

Re: [sqlite] Performance Problem

On the MC55 and MC70 we use with Sqlite 3.5.9: PRAGMA temp_store = MEMORY PRAGMA journal_mode = PERSIST PRAGMA journal_size_limit = 50 On 2/16/2011 5:24 AM, Black, Michael (IS) wrote: > Try this benchmark program and see what numbers you get. You need to compare > to other machines with the

Re: [sqlite] completion of sql words

I tihnk the firefox plug-in Sqlite Manager does. On 2/11/2011 2:30 PM, Simon Slavin wrote: > On 11 Feb 2011, at 7:19pm, prad wrote: > >> does the sqlite3 interface have completion of sql key words? >> in postgresql you can type SEL and press tab to complete. >> is there such a thing for sqlite3?

Re: [sqlite] Bi-directional unique

If you don't care about the order then use and instead of trigger to force the ordering then you will get the collisions you expect. On 2/9/2011 10:12 AM, Black, Michael (IS) wrote: > I have a need to create a unique bi-directional relationship. > > You can think of it as pairings of people who

Re: [sqlite] Disk I/O Error

Could a backup or virus scanning software be locking the database? On 2/8/2011 10:12 AM, Nathan Biggs wrote: > I haven't tried that, but if we stop the application then restart it, > everything works again. That is until we get another Disk I/O error > which happens the next day. > I'm wondering

Re: [sqlite] How do I query for a specific count of items?

Only apples SELECT distinct customerid FROM Customers c1 WHERE Type = 'Apple' AND not exists (select 1 from customers c2 where c2.customerid=c1.customerid and not Type = 'Apple') ; Apples and Bananas SELECT distinct customerid FROM Customers c1 WHERE Type = 'Apple' AND exists (select 1 from

Re: [sqlite] Question about database design

I'd probably move the analyze out of the loop. Since your joining on props.id a better index pind might be create index pind on props (id, pnam) The name of column id in table props would be clearer as obj_id since it is not the id of the property but the id of the record in the obj table. On

Re: [sqlite] SQL query on sort order

If not already done creating a page of additional collations on the wiki would make sense and minimize work all around. > I just mailed you an extension for SQLite offering the collation you need. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] How to optimize this simple select query ?

Did you try a compound index? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How Execute Joint Queries In Sqlite?

A union of a left and right joins should do it. On 12/7/2010 4:50 AM, Simon Slavin wrote: > On 7 Dec 2010, at 12:37pm, Pavel Ivanov wrote: > >>> I Writed A FULL OUTER JOIN Query In Sqlite But I Got A Message Like Not >>> Supported. What Should I Do To Do This >> You should re-think once more: do

Re: [sqlite] Select fails even though data is in the table.

ess is accessing the > data. > > This is a single process that reads data from message queue and dumps into > database to look for duplicate rows. > > The problem occurs for some rows only (about 3 to 5 an hour). > > > Hemant Shah > E-mail: hj...@yahoo.com > >

Re: [sqlite] Select fails even though data is in the table.

If you have another thread running that deletes or modifies the table, then move the commit to after the select for duplicate to ensure transactional integrity. On 12/1/2010 8:10 AM, Black, Michael (IS) wrote: > The problem is probably in the bind calls that you are not showing. > If you care

Re: [sqlite] Select fails even though data is in the table.

Why are you looking for a duplicate with col4 instead of the unique key, col2, col3 that caused the collision? On 12/1/2010 7:29 AM, Hemant Shah wrote: > Folks, > > My C program creates a in-memory database. It creates a table and a unique > index on two columns. If the insert fails due to

Re: [sqlite] joining two sequences?

.value from bar b3 WHERE b3.key = b1.key except -- Values common to both foo key and bar key select f2.value from foo f2 inner join bar b2 on b2.value = f2.value WHERE b2.key = b1.key AND f2.key= f1.key ); On 11/19/2010 6:40 PM, Jim Morris wrote: > This should return a the equivalent keys in the

Re: [sqlite] joining two sequences?

This should return a the equivalent keys in the two maps. The basic idea is to compare the values in each key in foo(left outer join foo) with the values for each key in bar where there are any matching values(left outer join bar) and only select those with a complete match( inner join). Not

Re: [sqlite] GROUP BY driving me crazy

If you would explain why/how the position value is significant that might help. I fixed your pseudo SQL to run in SQLite Manager and I don't understand from the sample data what your trying to do. There is only one image per item. Do you have multiple images per item and only want to return the

Re: [sqlite] GROUP BY driving me crazy

There is no logic way to show you intended result. You need some sort of data that can be used as a filter. If you want to filter by color why not add color to the item and use a WHERE clause? Or maybe style? ___ sqlite-users mailing list

Re: [sqlite] GROUP BY driving me crazy

Can you better explain your intent? Why are you grouping? This is normally for creating sums, averages, counts etc. Do you have a small sample of input vs output desired? On 11/10/2010 11:11 AM, James wrote: > I've been fighting with this for a couple days now. I've been > searching like mad,

Re: [sqlite] Comma-delimited field to rows (Once again)

Not much help but this removes the multiplication: SELECT B1.B + B2.B + B3.B + B4.B FROM (SELECT 0 AS B UNION SELECT 1 AS B) AS B1, (SELECT 0 AS B UNION SELECT 2 AS B) AS B2, (SELECT 0 AS B UNION SELECT 4 AS B) AS B3, (SELECT 0 AS B UNION SELECT 8 AS B) AS B4 On 10/15/2010 3:00 PM, Max Vlasov

Re: [sqlite] What is the most efficient way to get the close by numbers?

If there is an index on (name, position) the a where like below might use it. A1.name=A2.name and A2.position between( A1.position - 10, A1.position + 10 ) On 8/20/2010 3:54 PM, Peng Yu wrote: > Hi, > > I have the following code to search for neighboring positions > (distance<=10). But it is

Re: [sqlite] Is there a way to inner join on named intervals?

Did you try something like(pseudo code): select * from A inner join B on A.name = B.name AND ( B.left between(A.left,A.right) OR B.right between(A.left,A.right) ) On 8/13/2010 8:07 AM, Peng Yu wrote: > Hi, > > Suppose that I have a table "A", each row represents a interval. For > example, the

Re: [sqlite] Help with complex UPDATE question

lly [7, 8] - [1, 2]. > The question is can I do this just using SQL? > > > > > Jim Morris-4 wrote: > >> What you are trying to do is unclear to me. It seems that table1 >> doesn't have enough data to unambiguously identify the rows. >> >> On 7/23/2010 8:03 AM,

Re: [sqlite] Help with complex UPDATE question

What you are trying to do is unclear to me. It seems that table1 doesn't have enough data to unambiguously identify the rows. On 7/23/2010 8:03 AM, peterwinson1 wrote: > Thanks Eric and Alan for your help. I tried to apply your code to my problem > and it works to a limited extent because the

Re: [sqlite] sqlite database handle caching and write permissions

You also need to watch for multiple command separated via ';' On 7/15/2010 11:36 AM, JT Olds wrote: > I considered that also, but I wasn't sure about whether or not that > guaranteed no disk writes (maybe some sort of function call might be > made there). That also restricts things like the usage

Re: [sqlite] Compiling as part of MFC C++ project

You need to use the project properties to set the file as a C file and to not use precompiled headers On 7/13/2010 5:47 PM, GHCS Software wrote: > What do I need to do to get sqlite3.c to compile in a MFC C++ project > (Visual C++)? If I just add it to the project, I end up getting a > compile

Re: [sqlite] loading data from file with the file name as the extra field

Try creating a script file something like(Psuedo code): .separator "," CREATE TEMP TABLE dummy (value); .import file1.csv dummy INSERT INTO TEST (filename,number) (SELECT 'file1.csv', value FROM dummy; delete from dummy; .import file2.csv dummy INSERT INTO TEST (filename,number) (SELECT

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

Are you thinking of limit? On 7/2/2010 9:58 AM, Simon Slavin wrote: > On 2 Jul 2010, at 5:15pm, Peng Yu wrote: > > >> I want to select an entry that appears >> <=n times and only show n times if it appears more than n times. I >> think that "group by" might help. >> > There's no simple

Re: [sqlite] EXTERNAL:Re: How to select an entry that appears <=n times and only show n times if it appears more than n times?

Maybe this? SELECT, min(Count(type_id),n) FROM foods GROUP BY ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow query

You also haven't specified a primary key, i.e. on "id" On 6/28/2010 11:24 PM, J. Rios wrote: > I have the next table > > table1( id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER > ); > > I have created the next indexes : index1( name ), index2( id2 ), index3( > name2 ); > >

Re: [sqlite] marking transaction boundaries

I don't understand the driver for this but have you considered creating a function that would be called as part of the insert or trigger that would have greater access to application/sqlite internal info that might be used to create a transaction id.

[sqlite] Minor WAL document typo

http://www.sqlite.org/draft/wal.html 'a' should be 'as' in the text "located in the same directory or folder a the original database file" Should be "located in the same directory or folder as the original database file" ___ sqlite-users mailing list

Re: [sqlite] WHERE = does not work

Is is possible the character encoding is different? On 4/30/2010 6:59 AM, Adam DeVita wrote: > Is it possible there is a null, tab, newline or other invisible character? > Try > > select timeStamp, '' || resourceType || 'xx' From MyTable where > resourceType like 'PSM' LIMIT 10; > > On

Re: [sqlite] Query plan of MAX (id) versus MAX (id) + 1 in a range

Did you try something like: SELECT id + 1 FROM foo WHERE id>= 100 AND id< 200 and id = MAX (id); On 02/22/2010 7:02 AM, ArtemGr wrote: > Simon Slavin writes: > >> Just out of interest, and I know that theoretically this is not an optimal >> statement, but have you compared