Re: [sqlite] SQL question, move union to second column

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 > > >

Re: [sqlite] SQL question, move union to second column

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

Re: [sqlite] SQL question, move union to second column

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

Re: [sqlite] SQL question, move union to second column

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,

Re: [sqlite] SQL question, move union to second column

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

[sqlite] SQL question, move union to second column

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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,

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

> 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 =

[sqlite] SQL question

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

Re: [sqlite] SQL question - not sure if it's possible at all

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,

Re: [sqlite] SQL question - not sure if it's possible at all

"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? >>> >> >>

Re: [sqlite] SQL question - not sure if it's possible at all

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

[sqlite] SQL question - not sure if it's possible at all

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

Re: [sqlite] SQL question - not sure if it's possible at all

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

Re: [sqlite] SQL question - not sure if it's possible at all

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

[sqlite] SQL question - not sure if it's possible at all

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

Re: [sqlite] SQL question, probably stuipid but ...

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

[sqlite] SQL question, probably stuipid but ...

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 ___

Re: [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.

Re: [sqlite] SQL question with SQLite

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: [sq

[sqlite] SQL question with 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

Re: [sqlite] SQL question

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

[sqlite] SQL question

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

Re: [sqlite] SQL question

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,

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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,

[sqlite] SQL question

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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

[sqlite] SQL question regarding triggers updating values

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 -

Re: [sqlite] sql question

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

[sqlite] sql question

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

[sqlite] SQL question - grouping records and more

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

Re: [sqlite] SQL question

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:

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

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

Re: [sqlite] SQL question

rs@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, price double )

Re: [sqlite] SQL question

t; <[EMAIL 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(

[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 Ingredient I would like to create a new property for this

Re: [sqlite] SQL Question

> 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

[sqlite] SQL Question

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