Hello Simon,
From: Simon Slavin slav...@bigfraud.org
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
On 12/15/09 13:16 , Florian Schricker fschric...@gmail.com 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
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
On Tue, Dec 15, 2009 at 6:16 AM, Florian Schricker fschric...@gmail.com wrote:
Hello Simon,
From: Simon Slavin slav...@bigfraud.org
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.
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
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 = 'product'
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 above
Florian Schricker fschric...@gmail.com
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
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
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
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 find
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=?);
This works
Dennis Volodomanov i...@psunrise.com
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 Data
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, (select Date,
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 find
Dennis Volodomanov i...@psunrise.com 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,
Igor Tandetnik wrote:
Dennis Volodomanov i...@psunrise.com 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
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
___
To escape ' use cosecutive ' , i.e ''
On Wed, 18 Feb 2009 10:32:28 +0530, J. R. Westmoreland j...@jrw.org 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. R.
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
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] SQL question with SQLite
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's
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
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 all the
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,
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
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, it has
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 an id,
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
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_id from
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
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 values(
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 Little Bit Of
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 integer,
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 -
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 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
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
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
PROTECTED]
To: sqlite-users@sqlite.org
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
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
);
When I create a new
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
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
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 Properties and
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
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: sqlite-users@sqlite.org
Sent: Thursday, June 09, 2005 12:26 AM
Subject: Re: [sqlite] SQL question
Thanks
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
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 and
48 matches
Mail list logo
