all settings:
NameValue Modified
auto_vacuum noneFalse
automatic_index on False
cache_size 2000False
case_sensitive_like off False
collation_list [NOCASE], [RTRIM], [BINARY] False
count_changes off False
default_cache_size 2000False
Given the following table:
DROP TABLE IF EXISTS key_table5;
CREATE TABLE key_table5 (
name TEXT NOT NULL default '',
color TEXT default NULL,
price REAL default NULL,
UNIQUE (name),
PRIMARY KEY (color)
);
I'm getting the following exception:
SQLException: [SQLITE_ERROR] SQL error or missing
On 11 Nov 2011, at 2:23am, dmp wrote:
I'm getting the following exception:
SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC:
syntax error)
with:
SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2'
GROUP BY color ASC ORDER BY name ASC LIMIT
dmp da...@ttc-cmc.net wrote:
I'm getting the following exception:
SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax
error)
with:
SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2'
GROUP
BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0
On 11 Nov 2011, at 1:15pm, Igor Tandetnik wrote:
ASC is not valid with GROUP BY, only with ORDER BY. What is it supposed to
achieve, anyway? Your query makes no sense to me.
Igor, I started out to write exactly the same thing, but then I saw the diagram
on
SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price
'2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0
when either ASC or DESC is used with the GROUP BY clause.
Is this a bug or a query statement inaccuracy on my part?
Looks like a documentation bug. Indeed the
On Fri, Nov 11, 2011 at 02:24:54PM +0100, Jean-Christophe Deschamps scratched
on the wall:
Is this a bug or a query statement inaccuracy on my part?
Looks like a documentation bug. Indeed the select-core diagram
incorrectly shows an ordering-term in the GROUP BY clause. It
should
On Fri, Nov 11, 2011 at 8:20 AM, Simon Slavin slav...@bigfraud.org wrote:
On 11 Nov 2011, at 1:15pm, Igor Tandetnik wrote:
ASC is not valid with GROUP BY, only with ORDER BY. What is it supposed
to achieve, anyway? Your query makes no sense to me.
Igor, I started out to write exactly the
Suppose I have two tables, and I want to have look for a value in the first
table, but display the columns from the second table. The most obvious way
would be joining them on rowid. But I don't need to SELECT any columns from
the first table, and it's a FTS4 table (which always joins a bit slower
Given the following table:
DROP TABLE IF EXISTS key_table5;
CREATE TABLE key_table5 (
name TEXT NOT NULL default '',
color TEXT default NULL,
price REAL default NULL,
UNIQUE (name),
PRIMARY KEY (color)
);
I'm getting the following exception:
SQLException: [SQLITE_ERROR] SQL error or missing
I think you want ORDER BY COLOR,NAME
Since color is a primary key you can't have dups so what good is the group by?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
Simon Slavin wrote:
On 9 Nov 2011, at 8:03pm, Yuriy Kaminskiy wrote:
Look at: SELECT hex(X'1245005679'),hex(X'1245001234');
And compare: SELECT X'1245005679' LIKE X'1245001234'; 1 -- incorrect SELECT
X'1245005679' = X'1245001234'; 0 -- correct SELECT X'1245005679'
X'1245001234'; 1 --
Hi, I'm studying the indexing mechanism of FTS3/4, I can pretty much
understand how doclists, terms, segments are created and stored, but one
thing I can't grasp is about updating and deleting docs and keeping up the
index up to date. From the source comments:
[quote]
** Since we're using a
On Fri, Nov 11, 2011 at 11:55 AM, Yuriy Kaminskiy yum...@mail.ru wrote:
One way or other, =, LIKE and GLOB results should be consistent.
If string is NUL-terminated, = should ignore everything after NUL.
If string is length-terminated, LIKE should not ignore bytes after NUL.
blob = blob should
On Thu, Nov 10, 2011 at 3:19 AM, yqpl y...@poczta.onet.pl wrote:
i did some test do check if indexes make it slow. instead of inserting to
disk database i use :memory: database - i have copied tables only - i
assume without indexes and then do inserts - and it works the same.
UNIQUE
On Fri, Nov 11, 2011 at 1:39 AM, yqpl y...@poczta.onet.pl wrote:
Nico Williams wrote:
What's your page size?
i have no access now to those files. but i didnt change any thing - so
default.
You really want to set the page size to something decent -- at least
the filesystem's preferred block
Message: 23
Date: Fri, 11 Nov 2011 10:57:22 -0500
From: da...@dandymadeproductions.com
To: sqlite-users@sqlite.org
Subject: [sqlite] GROUP BY With ASC/DESC
Message-ID:
5ed601b698a020a8d790240cc05c8714.squir...@dandymadeproductions.com
Content-Type: text/plain;charset=iso-8859-1
On 11 Nov 2011, at 6:09pm, Nico Williams wrote:
blob = blob should be a binary comparison
blob = string should be a string comparison
blob LIKE pattern should either treat the blob as a string or not, but
I don't see why either behavior should imply that blob = blob should
be anything
My db has a column called status. This can take one of 7 or so integer values
from 0 to 7 or so. There are times when I need a quick answer to this question:
are there any rows in the db for which status has value 0. I don't need to know
how many, just whether there are any or not. What's the
On 11 Nov 2011, at 7:27pm, Tim Streater wrote:
select count(status) from mytable where status=0;
select count(status) from mytable where status=0 limit 1;
select status from mytable where status=0 limit 1;
When doing this a number of times I see some seconds of CPU being taken; I
On Fri, Nov 11, 2011 at 1:16 PM, Simon Slavin slav...@bigfraud.org wrote:
On 11 Nov 2011, at 6:09pm, Nico Williams wrote:
blob = blob should be a binary comparison
blob = string should be a string comparison
blob LIKE pattern should either treat the blob as a string or not, but
I don't see
yes still slows down.
Nico Williams wrote:
On Thu, Nov 10, 2011 at 3:19 AM, yqpl y...@poczta.onet.pl wrote:
i did some test do check if indexes make it slow. instead of inserting to
disk database i use :memory: database - i have copied tables only - i
assume without indexes and then do
On Fri, Nov 11, 2011 at 2:38 PM, yqpl y...@poczta.onet.pl wrote:
yes still slows down.
Can you characterize it? All index inserts should slow down somewhat
as the index grows since lookup and insertion will be O(logN)
operations for b-trees, but also as your indexes grow larger than
available
On Fri, Nov 11, 2011 at 00:52:23 +0100, Fabian wrote:
2011/11/10 Richard Hipp d...@sqlite.org
Because when you are inserting the Nth row, SQLite has no idea of how many
more rows will follow or how big the subsequent rows will be, so it has no
way to reserve contiguous space sufficient to
On Fri, Nov 11, 2011 at 9:58 AM, nobre rafael.ro...@novaprolink.com.br wrote:
Hi, I'm studying the indexing mechanism of FTS3/4, I can pretty much
understand how doclists, terms, segments are created and stored, but one
thing I can't grasp is about updating and deleting docs and keeping up the
Hi,
I have a strange problem with an SQL query. I want to filter records by
the result of a grouped column. SQLite doesn't complain but gives no
result records in PHP PDO but the very same SQL does work fine in the
SQLite shell on Windows. I have version 3.6.22 and an older 3.6 on the
PHP side
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
time at random?
Thanks, this may be a newbie question for
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
On 11/11/2011 7: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.
Make it bind, step and reset. Don't leave a statement active for a long
time - it keeps the
select count(status) from mytable where status=0;
select count(status) from mytable where status=0 limit 1;
These two are identical because query always return one row thus
making limit 1 a no-op.
select status from mytable where status=0 limit 1;
This one will have the best performance
On Fri, Nov 11, 2011 at 11:58 AM, Petite Abeille
petite.abei...@gmail.com wrote:
It returns the same results, but it doesn't seem much faster. Is there any
performance difference to be expected from using IN instead of JOIN, or
does SQLite internally rewrite JOIN queries to something similar as
On Nov 12, 2011, at 1:58 AM, Pavel Ivanov wrote:
No, exists in this case will change query plan significantly and
performance can degrade drastically as a result.
Why would that be?
___
sqlite-users mailing list
sqlite-users@sqlite.org
Fabian wrote:
Suppose I have two tables, and I want to have look for a value in the first
table, but display the columns from the second table. The most obvious way
would be joining them on rowid. But I don't need to SELECT any columns from
the first table, and it's a FTS4 table (which always
Hi everybody,
Here is a simplified version of the statement I try to run (let a, b,
and m be tables with only one column named c containing integers):
SELECT * FROM m WHERE
c IN (SELECT * FROM a) OR
c IN (SELECT * FROM b)
AND (NOT c IN (SELECT * FROM a)
OR c IN (SELECT *
On Fri, Nov 11, 2011 at 8:06 PM, Petite Abeille
petite.abei...@gmail.com wrote:
No, exists in this case will change query plan significantly and
performance can degrade drastically as a result.
Why would that be?
How would you rewrite the query using exists? The only thing I have in mind is
I know this could be done more easily, but I have to keep this
structure (i.e. cannot JOIN for instance). My question is, how could I
avoid repeating the subqueries after the AND NOT, since they will
return the same set as the previous ones?
With your restriction on query structure you cannot
On 11/11/2011 9:24 PM, Alexandre Courbot wrote:
Here is a simplified version of the statement I try to run (let a, b,
and m be tables with only one column named c containing integers):
SELECT * FROM m WHERE
c IN (SELECT * FROM a) OR
c IN (SELECT * FROM b)
AND (NOT c IN (SELECT *
Hi all,
We are struggling to find a way to rework this query in a way
that performs efficiently for large data sets.
The goal is to find the top x most active senders of e-mails
within a date range.
The killer of this query is the ORDER BY clause. Without it
the results are quick and snappy.
On 12 Nov 2011, at 3:43am, Josh Gibbs wrote:
We are struggling to find a way to rework this query in a way
that performs efficiently for large data sets.
It's all about the indexes.
The goal is to find the top x most active senders of e-mails
within a date range.
Do you frequently do
39 matches
Mail list logo
