Ah, yes, thanks.
RBS
On Thu, Mar 14, 2019 at 12:55 AM Igor Tandetnik wrote:
> On 3/13/2019 8:32 PM, Bart Smissaert wrote:
> > Sorry, ignore that, can see now that all is a reserved word.
>
> You can enclose it in double quotes, as in "All", if you really want it.
> --
> Igor Tandetnik
>
>
> ___
On 3/13/2019 8:32 PM, Bart Smissaert wrote:
Sorry, ignore that, can see now that all is a reserved word.
You can enclose it in double quotes, as in "All", if you really want it.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglis
Sorry, ignore that, can see now that all is a reserved word.
RBS
On Thu, Mar 14, 2019 at 12:30 AM Bart Smissaert
wrote:
> Thanks; this works fine:
>
> select PLACE,
> sum(ID not in (select ID from ATTENDED)),
> count(ID)
> from PERSONS group by PLACE
>
> But if I add the aliases I get:
Thanks; this works fine:
select PLACE,
sum(ID not in (select ID from ATTENDED)),
count(ID)
from PERSONS group by PLACE
But if I add the aliases I get:
near "All": syntax error
Result of sqlite3_prepare16_v3: 1
select PLACE, sum(ID not in (select ID from ATTENDED)) Not_Attended,
count(*)
On 3/13/2019 8:08 PM, Bart Smissaert wrote:
But I would like the result to be in 3 columns, so result in this case
would be:
Place Not_Attended All
---
A 3 7
B 2 3
Something like this (not tested):
select PLACE,
Have 2 tables:
PERSONS:
CREATE TABLE [PERSONS]([ID] INTEGER PRIMARY KEY, [Place] TEXT)
ATTENDED:
CREATE TABLE [ATTENDED]([ID] INTEGER)
Sample date like this:
PERSONS:
ID Place
---
1 A
2 A
3 B
4 A
5 A
6 A
7 B
8 B
9 A
10 A
ATTENDED:
ID
-
1
5
6
1
1
8
9
5
1
5
8
1
6
8
9
9
1
5
6
1
On Tue, Dec 15, 2009 at 6:16 AM, Florian Schricker wrote:
> Hello Simon,
>
>
>> From: Simon Slavin
>> On 14 Dec 2009, at 8:52am, Florian Schricker wrote:
>>
> Primary keys are Oper, Product, Category, Name and CreateTS
There is only one primary key per table.
>>>
>>> So to say the prim
Florian Schricker wrote:
> Hello Simon,
>
SNIP
> Please excuse me for being so blunt:
> You have no idea what I'm supposed to do here nor do you have any idea
> for whatever historic reason the database or table is designed "as is"
> here nor (and finally) do you have no idea about my education
On 12/15/09 13:16 , "Florian Schricker" wrote:
> Please excuse me for being so
> blunt:
You have no idea what I'm supposed to do here nor do you have any
> idea
for whatever historic reason the database or table is designed "as
> is"
here nor (and finally) do you have no idea about my education
Hello Simon,
> From: Simon Slavin
> On 14 Dec 2009, at 8:52am, Florian Schricker wrote:
>
Primary keys are Oper, Product, Category, Name and CreateTS
>>> There is only one primary key per table.
>>
>> So to say the primary key is "Oper, Product, Category, Name, CreateTS".
>
> No.
> Go rea
If I understand your question correctly, Florian, you want the most
recent Value for each entity represented by the composite primary key
{oper, product, category, name}.
To find the rows that contain the most recent values (although not yet
the values themselves) you first need to aggregate by
On 11 Dec 2009, at 1:13pm, Florian Schricker wrote:
> - CreateTS (Timestamp)
No such type in SQLite. Take a look at
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
http://www.sqlite.org/lang_datefunc.html
I'd recommend you store your time/date data either as strings (which would be
Florian Schricker
wrote:
> Schema of DB (simplified):
> - Oper (string)
> - Product (string)
> - Category (string)
> - Name (string)
> - CreateTS (Timestamp)
> - Value (Double)
>
> Primary keys are Oper, Product, Category, Name and CreateTS
>
> What I could not figure out: Suppose I want to sel
On Fri, 11 Dec 2009, Florian Schricker wrote:
> Schema of DB (simplified):
> - Oper (string)
> - Product (string)
> - Category (string)
> - Name (string)
> - CreateTS (Timestamp)
> - Value (Double)
Florian,
The schema refers to the set of tables, and the attributes within each
table. Is the a
> So for a set of measurements identified by everything but their name
> select the latest rows as defined by the timestamp.
>
> Can I do that in SQL in one query?
Maybe this is what you want:
select Name, Value, CreateTS from TableName
where Oper = 'op'
and Category = 'cat'
and Product = 'produc
Hi everyone!
I'm a starter on SQL / SQLite and there is some problem I'd solve in
software but I have the feeling this can be done using a query. If
somebody can help me out I'd be glad - I have the feeling there is
something to learn for me here.
Here goes:
Schema of DB (simplified):
- Oper (
Igor Tandetnik wrote:
> Well, there's no way to tell how records with the same Date are going to
> end up ordered in the original query. If you impose some deterministic
> order there, e.g. with
>
> ORDER BY Date, Id
>
> then you can do something like this:
>
> select count(*)
> from Data d, (sel
"Dennis Volodomanov"
wrote in message news:4a1e80dd.9040...@psunrise.com
> Igor Tandetnik wrote:
>>> and let's say, "Data" has an "ID" field (primary index).
>>>
>>> Is it possible to find out the *position* in the returned result set
>>> of a Data with a given ID?
>>>
>>
>> select count(*) from D
Igor Tandetnik wrote:
>> and let's say, "Data" has an "ID" field (primary index).
>>
>> Is it possible to find out the *position* in the returned result set
>> of a Data with a given ID?
>>
>
> select count(*) from Data
> where PlotOnGraph=1
> and Date < (select Date from Data where ID=?);
Hello all,
I'm not sure if this can be done in SQL, but if it can, I'd appreciate
your help.
Let's say I've got this query:
SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC)
LIMIT ?2 OFFSET ?1
and let's say, "Data" has an "ID" field (primary index).
Is it possible to f
Igor Tandetnik wrote:
> Dennis Volodomanov wrote:
>
>> Let's say I've got this query:
>>
>> SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date
>> ASC) LIMIT ?2 OFFSET ?1
>>
>
> Why an extra layer? Why not just
>
> SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC
>
Dennis Volodomanov wrote:
> Let's say I've got this query:
>
> SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date
> ASC) LIMIT ?2 OFFSET ?1
Why an extra layer? Why not just
SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC
LIMIT ?2 OFFSET ?1
> and let's say, "Data" has a
Hello all,
I'm not sure if this can be done in SQL, but if it can, I'd appreciate
your help.
Let's say I've got this query:
SELECT * FROM (SELECT * FROM Data WHERE PlotOnGraph=1 ORDER BY Date ASC)
LIMIT ?2 OFFSET ?1
and let's say, "Data" has an "ID" field (primary index).
Is it possible to f
To escape "'" use cosecutive "'" , i.e "''"
On Wed, 18 Feb 2009 10:32:28 +0530, J. R. Westmoreland wrote:
> I can't remember how to escape a "'" in a string.
>
> My statement looks something like:
>
> "insert . values ('DAY'S AVE' .);
>
>
> It's been a long day.
>
> Thanks in advance.
>
>
> J.
I can't remember how to escape a "'" in a string.
My statement looks something like:
"insert . values ('DAY'S AVE' .);
It's been a long day.
Thanks in advance.
J. R.
J. R. Westmoreland
E-mail: j...@jrw.org
___
sql
On Tue, Dec 09, 2008 at 04:58:38PM +0100, jm cuaz scratched on the wall:
> In a SELECT statement with multiple tables, is it possible to replace
> WHERE clauses used to avoid cross joins with GROUP BY + HAVING clauses
> (with the same criteria) for the same purpose (no cross join) ?
No. That
If you want help with your SQL. You should post it along with your question.
There are a lot of really good SQL experts on here that will be able to help
you.
--- On Tue, 12/9/08, jm cuaz <[EMAIL PROTECTED]> wrote:
> From: jm cuaz <[EMAIL PROTECTED]>
> Subject: [sqlite]
Hello,
In a SELECT statement with multiple tables, is it possible to replace
WHERE clauses used to avoid cross joins with GROUP BY + HAVING clauses
(with the same criteria) for the same purpose (no cross join) ?
Are the two methods roughly equivalent in performance ?
We ask this because we won
Petite Abeille <[EMAIL PROTECTED]>
wrote:
> Given a set of ids, what would be the proper way to find the records
> containing all those ids?
>
> Specifically, given a 'document_token' table containing a document_id
> mapping to multiple token_id, how would one find the document_id which
> contains
Hello,
Not specific to sqlite, but a rather generic SQL question...
Given a set of ids, what would be the proper way to find the records
containing all those ids?
Specifically, given a 'document_token' table containing a document_id
mapping to multiple token_id, how would one find the docume
I did change it to:
UPDATE `table` SET `id` = `id` + 32768 WHERE `id` >= x
and then I decrement everything over 32768 by 32767 to get it back in
line.
This was required because a single update on a primary key did return
an error about a key conflict when only incrementing by 1!
Thanks,
Gre
Gregor, why did you do that more complicated version with the subquery and
sorting et al? The short version that RBS would have worked a lot better;
you just need to say?
update binary_report_fmt
set column_id = column_id + 1
where column_id > 1;
... and then insert a new row with col
On Mon, 16 Jun 2008 10:36:18 -0600, you wrote:
>Hi, thanks this is great. Of course I forgot to mention that the id
>is a primary key, so I get an error about duplicate primary keys.
>
>I tried this:
>
>update binary_report_fmt set column_id = column_id + 1 where column_id
>= (select column_i
Hi, thanks this is great. Of course I forgot to mention that the id
is a primary key, so I get an error about duplicate primary keys.
I tried this:
update binary_report_fmt set column_id = column_id + 1 where column_id
= (select column_id from binary_report_fmt where column_id >= 3 order
b
update
table
set id = id + 1
WHERE
id > 1
RBS
> Sorry about this, but this is a SQL question and not a SQLite specific
> question.
>
> Is there a way to increment a value in a table in-situ. Without
> reading it, incrementing it, writing it?
>
> I need to insert an entry into a table, it has a
On 6/16/08, Gregor Brandt <[EMAIL PROTECTED]> wrote:
> Sorry about this, but this is a SQL question and not a SQLite specific
> question.
>
> Is there a way to increment a value in a table in-situ. Without
> reading it, incrementing it, writing it?
>
> I need to insert an entry into a table,
Sorry about this, but this is a SQL question and not a SQLite specific
question.
Is there a way to increment a value in a table in-situ. Without
reading it, incrementing it, writing it?
I need to insert an entry into a table, it has an id, all entries with
id's >= the id need to be increm
Hi Simon,
it works very well.
Now I study it and I try to understand why it works :-)
Thanks a lot
Andrea
Simon Davies ha scritto:
> Andrea,
>
> This appears to do what you want...
>
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite>
> sqlite> create table tst( name text, score
sqlite> create table t1(n,c);
sqlite> insert into t1 values("a",3);
sqlite> insert into t1 values("a",5);
sqlite> insert into t1 values("b",7);
sqlite> insert into t1 values("b",2);
sqlite> select * from t1;
a|3
a|5
b|7
b|2
sqlite> select n,max(c) from t1 group by n;
a|5
b|7
--
[image: Just A Lit
Andrea,
This appears to do what you want...
SQLite version 3.4.2
Enter ".help" for instructions
sqlite>
sqlite> create table tst( name text, score integer, info text );
sqlite> insert into tst values( 'A', 289, 'A1' );
sqlite> insert into tst values( 'C', 29, 'C1' );
sqlite> insert into tst value
Hello all,
Let's say I have this schema:
CREATE TABLE Table1 (FileID INTEGER NOT NULL, FileOrder INTEGER);
And I need to go through it at change FileOrder so that it becomes
FileOrder of the next (or previous) FileID and that's FileID FileOrder
becomes current (to put it in words - swap
Uma Venkataraman wrote:
I want to recycle the table for which I need to be able to delete the
first 100 records from a table and add 100 new records.
Thanks
Uma,
See my reply to a similar question about FIFO tables in the archives at
http://article.gmane.org/gmane.comp.db.sqlite.general/1
I want to recycle the table for which I need to be able to delete the first
100 records from a table and add 100 new records.
Thanks
Hi,
I have table with the fields id, name and number. In the table there are
several records with the same id and name but different numbers. For
example:
First record - id: 1, name: John, number: 5
Second record - id: 1, name: Joe, number: 4
Third record - id: 2, name: Richard, number: 1
I want
Sorry, stupit mistak, I have to use NEW and not OLD in case of an INSERT
trigger!
Thanks all it works like a breeze!
- Original Message -
From: "Lloyd Dupont" <[EMAIL PROTECTED]>
To:
Sent: Thursday, June 09, 2005 12:26 AM
Subject: Re: [sqlite] SQL question
Thanks for that!
last_insert_rowid() function:
anyway, I hadn't tested the code. I mean the CREATE TRIGGER succeed.
But I didn't check if the trigger itself works well.
Now I did and have a problem...
It don't work!
I get: "SQLite Error 1 - no such column: OLD.ID"
this is my setting:
CREATE T
Martin Engelschalk <[EMAIL PROTECTED]> writes:
> Hi Lloyd,
>
> i am not sure, but i checked the documentation and i don't think it ist
> sound.
> In your Update, the ROWID semms to refer to ingredients.rowid. However,
> you want to set properties.rowid.
> Can it be that it works, because Propert
Hi Lloyd,
i am not sure, but i checked the documentation and i don't think it ist
sound.
In your Update, the ROWID semms to refer to ingredients.rowid. However,
you want to set properties.rowid.
Can it be that it works, because Properties and Ingredients happen to
have the same number of rows?
thanks Martin it worked!
although I replaced your (SELECT MAX(ID) FROM Properties) by ROWID.
is it sound?
like that:
CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients
BEGIN
INSERT INTO Properties (price) VALUES (NULL);
UPDATE Ingredients
SET property_ID = ROWID
ECTED]>
To:
Sent: Wednesday, June 08, 2005 10:37 PM
Subject: [sqlite] SQL question
I have 2 related table:
CREATE TABLE Ingredients(
ID INTEGER PRIMARY KEY,
name TEXT,
description BLOB,
property_ID INTEGER
);
CREATE TABLE Properties(
ID INTEGER PRIMARY KEY,
price double
);
loyd Dupont" <[EMAIL PROTECTED]>
To:
Sent: Wednesday, June 08, 2005 10:37 PM
Subject: [sqlite] SQL question
I have 2 related table:
CREATE TABLE Ingredients(
ID INTEGER PRIMARY KEY,
name TEXT,
description BLOB,
property_ID INTEGER
);
CREATE TABLE Properties(
ID INTEG
I have 2 related table:
CREATE TABLE Ingredients(
ID INTEGER PRIMARY KEY,
name TEXT,
description BLOB,
property_ID INTEGER
);
CREATE TABLE Properties(
ID INTEGER PRIMARY KEY,
price double
);
When I create a new Ingredient I would like to create a new property for this
ing
> CREATE TABLE teams (id,name);
> CREATE TABLE games (id, date, team1_id, team2_id, result);
>
> team1_id and team2_id refer to the id in the teams table.
>
> 1. What query would be best suited to get an output so that the
> output would contain the teams names (not only the id) and the
> dates a
Hello,
I know this is not directly related to SQLite, but hopefully
someone has the time to help me with a (probably) basic SQL question:
Consider the following tables:
CREATE TABLE teams (id,name);
CREATE TABLE games (id, date, team1_id, team2_id, result);
team1_id and team2_id refer to the id
54 matches
Mail list logo