Re: [sqlite] Using COUNT(*) in unusual circumstances

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/10/11 18:52, Simon Slavin wrote: > I'll just use LIMIT 51 on my query and if I get 51 results back I'll > know there are too many for the query to be useful. Incidentally you don't need to add a limit on the query. In the code that calls

Re: [sqlite] Using COUNT(*) in unusual circumstances

On 8 Oct 2011, at 2:27am, Roger Binns wrote: > Although various people keep mentioning it I haven't seen you acknowledge > using EXPLAIN QUERY PLAN. It would be nice to hear if it turns out to be > helpful especially as it doesn't require mucking with the query itself. Yes it was a good

Re: [sqlite] Using COUNT(*) in unusual circumstances

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/10/11 15:52, Simon Slavin wrote: > But I was sure I had read that in SQLite the full result set was > retrieved even if LIMIT was used. For the outer query all LIMIT does is cause sqlite3_step to return done after limit number of calls. ie you

Re: [sqlite] Using COUNT(*) in unusual circumstances

On Fri, Oct 7, 2011 at 5:52 PM, Simon Slavin wrote: > Okay, I understand why defining an ORDER BY requires the entire result set to > be retrieved.  I had intended to remove ORDER BY when I used COUNT(*), though > I didn't mention that. If the ORDER BY can be satisfied

Re: [sqlite] System.Data.SQLite Exception with wrong password

Marco Cosentino wrote: > > The ErrorCode is set to "NotADatabase". > Wouldn't it more correct if this code is set to something like > SQLiteErrorCode.Auth or the Exception is more specialized? > The exceptions thrown by System.Data.SQLite reflect the underlying error code returned from the

Re: [sqlite] Using COUNT(*) in unusual circumstances

On Fri, 7 Oct 2011 19:57:09 +0100, Simon Slavin wrote: >On 7 Oct 2011, at 7:17pm, Roger Binns wrote: > >> On 07/10/11 09:52, Simon Slavin wrote: >>> "Do you really want to see all 50,000 entries that that search would >>> return ?". If this kind of search returns more than

Re: [sqlite] Sqlite WPF application (designer view) toolbox greyed out

Christoph P.U. Kukulies wrote: > > Any clues why this doesn't work for WPF apps? > As far as I know, there is no specific designer support for WPF in the System.Data.SQLite project (yet). However, you can of course still use all the System.Data.SQLite ADO.NET classes from inside a WPF project

Re: [sqlite] Using COUNT(*) in unusual circumstances

On 7 Oct 2011, at 10:27pm, Roger Binns wrote: > On 07/10/11 11:57, Simon Slavin wrote: >> My problem really seems to be with the way SQLite implements LIMIT n. >> If I understand previous posts on this list correctly, instead of >> finding only the first n records, it does the entire search

Re: [sqlite] how do I cd on Mac OX 10?

On Oct 7, 2011, at 5:54 PM, TR Shaw wrote: > > On Oct 7, 2011, at 5:46 PM, Mary Andes wrote: > >> Last login: Fri Oct 7 17:26:34 on console >> Mary-Andess-MacBook-Air:~ mjandes$ sqlite3 >> SQLite version 3.7.5 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >>

Re: [sqlite] how do I cd on Mac OX 10?

On Oct 7, 2011, at 11:46 PM, Mary Andes wrote: > Can anyone help me? You cannot 'cd' from inside SQLite shell. If you wish to open a specific database, simply point sqlite3 to it before hand: % sqlite3 /path/to/my/db http://www.sqlite.org/sqlite.html

Re: [sqlite] how do I cd on Mac OX 10?

On Oct 7, 2011, at 5:46 PM, Mary Andes wrote: > Last login: Fri Oct 7 17:26:34 on console > Mary-Andess-MacBook-Air:~ mjandes$ sqlite3 > SQLite version 3.7.5 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> cd /Users/mjandes/SQLite3 and Hedis > ...> > I

[sqlite] how do I cd on Mac OX 10?

Last login: Fri Oct 7 17:26:34 on console Mary-Andess-MacBook-Air:~ mjandes$ sqlite3 SQLite version 3.7.5 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> cd /Users/mjandes/SQLite3 and Hedis ...> I have a Mac Book Air. Lion operating system. I can get onto the

Re: [sqlite] Using COUNT(*) in unusual circumstances

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/10/11 11:57, Simon Slavin wrote: > My problem really seems to be with the way SQLite implements LIMIT n. > If I understand previous posts on this list correctly, instead of > finding only the first n records, it does the entire search first,

Re: [sqlite] Using COUNT(*) in unusual circumstances

On Fri, Oct 7, 2011 at 1:17 PM, Roger Binns wrote: > On 07/10/11 09:52, Simon Slavin wrote: >> "Do you really want to see all 50,000 entries that that search would >> return ?".  If this kind of search returns more than 100 records, >> there's no point in doing it at all. >

Re: [sqlite] Using COUNT(*) in unusual circumstances

On 7 Oct 2011, at 7:17pm, Roger Binns wrote: > On 07/10/11 09:52, Simon Slavin wrote: >> "Do you really want to see all 50,000 entries that that search would >> return ?". If this kind of search returns more than 100 records, >> there's no point in doing it at all. > > You can solve this at

Re: [sqlite] Using COUNT(*) in unusual circumstances

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/10/11 09:52, Simon Slavin wrote: > "Do you really want to see all 50,000 entries that that search would > return ?". If this kind of search returns more than 100 records, > there's no point in doing it at all. You can solve this at the user

Re: [sqlite] Using COUNT(*) in unusual circumstances

Puneet Kishor writes: > Especially, note Pavel's recent, wonderful explanation of how SQLite steps > through the result set handing you the data row by row. It *has* to go > through the set to know how big the set is... there is no way around it. > Actually, sometimes there are. Consider:

Re: [sqlite] Using COUNT(*) in unusual circumstances

On Oct 7, 2011, at 11:52 AM, Simon Slavin wrote: > > On 7 Oct 2011, at 5:31pm, Puneet Kishor wrote: > >> assuming you have some other application level language you are using to get >> the data, you could stuff the result set into an array and then report the >> highest index of the array

Re: [sqlite] Using COUNT(*) in unusual circumstances

On 7 Oct 2011, at 5:31pm, Puneet Kishor wrote: > assuming you have some other application level language you are using to get > the data, you could stuff the result set into an array and then report the > highest index of the array which might be faster than doing a second query > for just

Re: [sqlite] Can pre-sorted data help?

On 7 Oct 2011, at 5:30pm, Mohit Sindhwani wrote: > I have been trying to see how we can make one of our databases more space > efficient. I am now looking at seeing if we have the right indexes and if > there is a way to save space by removing indexes. > > Is there some way to make use of

Re: [sqlite] Can pre-sorted data help?

On Fri, Oct 7, 2011 at 12:30 PM, Mohit Sindhwani wrote: > Hi All, > > I have been trying to see how we can make one of our databases more space > efficient. I am now looking at seeing if we have the right indexes and if > there is a way to save space by removing indexes. > Have

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','a B','a C' > WHERE NOT

Re: [sqlite] Using COUNT(*) in unusual circumstances

On Oct 7, 2011, at 9:46 AM, Simon Slavin wrote: > > On 7 Oct 2011, at 2:19pm, Igor Tandetnik wrote: > >> SQLite has a non-standard extension whereby aliases assigned to expressions >> in the SELECT clause may be used in the WHERE and other clauses: >> >> select 1+2 as alias from mytable

[sqlite] Can pre-sorted data help?

Hi All, I have been trying to see how we can make one of our databases more space efficient. I am now looking at seeing if we have the right indexes and if there is a way to save space by removing indexes. Is there some way to make use of the fact that the data can be sorted by a specific

Re: [sqlite] Using COUNT(*) in unusual circumstances

On 7 Oct 2011, at 2:19pm, Igor Tandetnik wrote: > SQLite has a non-standard extension whereby aliases assigned to expressions > in the SELECT clause may be used in the WHERE and other clauses: > > select 1+2 as alias from mytable where alias > 0; Arg ! Okay, so I guess the form >

Re: [sqlite] SQLITE LIMIT clause

Be aware that if you do not specify an ORDER BY clause, the order of the returned rows are undefined. You might not even end up with rows with a primary key even near 100. What you probably want is: SELECT * FROM test WHERE PK1 > 100 LIMIT 100 ORDER BY PK1 ASC; Other than that, those two

Re: [sqlite] SQLITE LIMIT clause

> SELECT * from test WHERE PK1>100 AND PK1<200;>> SELECT * from test WHERE > PK1>100 LIMIT 100;>> Will the above queries have the same effect? Or will > LIMIT behave> differently, i.e. get the entire result set and then return the > first 100> from it? If your PK1 has no gaps then those two

[sqlite] SQLITE LIMIT clause

I have a table called test and it has about 50 columns ( about 200 bytes of data, all columns combined). I need to browse the entire table periodically. I have a primary key PK1 which basically is a increasing sequence number. SELECT * from test WHERE PK1>100 AND PK1<200; SELECT * from test

Re: [sqlite] Using COUNT(*) in unusual circumstances

WHERE or HAVING clauses that refer back to named results could be a problem with the simple replacement. SELECT a,b,a+b AS ab FROM t WHERE ab>10 Igor's suggestion work there too. --David Garfield Simon Slavin writes: > I'm trying to write some code which has to be useful under many

[sqlite] SQLite

Hey kids. Looking at the choices given at http://www.sqlite.org/download.html, I would like to know what would be the best way to add SQLite to my app. I am using Windows 7-64bit as the OS and Code::Blocks with MinGW/GNU C++. ___ sqlite-users

Re: [sqlite] Using COUNT(*) in unusual circumstances

On 7 Oct 2011, at 2:19pm, Igor Tandetnik wrote: [useful stuff] Thanks Igor. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using COUNT(*) in unusual circumstances

Simon Slavin wrote: > I'm trying to write some code which has to be useful under many different > circumstances. Something I want to be able to do is to > take an arbitrary SELECT statement and replace the columns which would > normally be returned with COUNT(*) to find

[sqlite] Using COUNT(*) in unusual circumstances

I'm trying to write some code which has to be useful under many different circumstances. Something I want to be able to do is to take an arbitrary SELECT statement and replace the columns which would normally be returned with COUNT(*) to find out how many rows would be returned. To do this I

Re: [sqlite] Big FLOAT bug

> Changing the 2 "15g" entries in sqlite3.c to "16g" corrects this problem. 15 > digits is all that is guaranteed but the vast majority of 16-digit values are > representable. > > Is this a valid solution?  Or are there other side effects? It should be ok. However there's another bug that will

Re: [sqlite] Meaning of sqlite3_wal_checkpoint_v2 parameters

On 10/06/2011 02:29 PM, David Barrett wrote: Hi! Can you help me understand more exactly what the output parameters are from sqlite3_wal_checkpoint_v2()? Specifically: 1) What is the relationship between pnLog and pnCkpt: is pnLog>=pnCkpt always true? Yes. Always true. 2) Under what