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

[Bart Smissaert]

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
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Bart Smissaert]

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:
>
> near "All": syntax error
> Result of sqlite3_prepare16_v3: 1
> select PLACE, sum(ID not in (select ID from ATTENDED)) Not_Attended,
> count(*) All from PERSONS group by PLACE
>
> Otherwise very neat indeed though!
>
> RBS
>
>
>
> On Thu, Mar 14, 2019 at 12:21 AM Igor Tandetnik 
> wrote:
>
>> 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,
>>sum(ID not in (select ID from ATTENDED)) Not_Attended,
>>count(*) All
>> from PERSONS group by PLACE;
>>
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Bart Smissaert]

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(*) All from PERSONS group by PLACE

Otherwise very neat indeed though!

RBS



On Thu, Mar 14, 2019 at 12:21 AM Igor Tandetnik  wrote:

> 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,
>sum(ID not in (select ID from ATTENDED)) Not_Attended,
>count(*) All
> from PERSONS group by PLACE;
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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,
  sum(ID not in (select ID from ATTENDED)) Not_Attended,
  count(*) All
from PERSONS group by PLACE;

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL question, move union to second column

[Bart Smissaert]

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

Now I would like to show the counts of persons that not attended, grouped
by place and in a third column the counts of all persons, again grouped by
place.

I can do it differently in 2 columns with a union:

SELECT P.PLACE, COUNT(P.ID) AS P_COUNT FROM PERSONS P LEFT JOIN ATTENDED A
ON(P.ID = A.ID)
WHERE A.ID IS NULL GROUP BY P.PLACE
UNION ALL
SELECT PLACE, COUNT(ID) AS P_COUNT FROM PERSONS
GROUP BY PLACE

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

Probably simple, but I can't work it out and thanks for any assistance.

RBS
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[P Kishor]

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 primary key is "Oper, Product, Category, Name, CreateTS".
>>
>> No.
>> Go read a boos on databases.  The shorter your primary key is, the faster 
>> everything works.  You want a very short primary key.  There would be no 
>> point in putting 'CreateTS' in your primary key unless it's possible for two 
>> records to exist which have the same "Oper, Product, Category, Name" but 
>> different CreateTS.
>> I might guess that your primary key is probably just Product.  If it's not 
>> possible to have two records with the same 'Product' then that is your 
>> primary key.
>
> 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
> databases or SQL or SQLite in general or in detail so please(!) stop
>


You did say in your original post that you are, "a starter on SQL /
SQLite and there is some problem (you would) solve in software but
(you) have the feeling this can be done using a query. If somebody can
help (you) out (you would) be glad - (you) have the feeling there is
something to learn for (you) here."


Here is the thing 1: Simon, or anyone's advice on this list, is free.
The most you should do with it if you disagree with it, unless the
person is being downright abusive, is to ignore it. You will lose
nothing. Simon's advice, on the other hand, was actually very good.
You would do well to heed it.

Thing 2: Your schema, even if we know nothing about your problem,
demonstrates issues that could bite you in the butt later on. First,
instead of having a composite PK made of 5 columns, you would really
be better off adding a new INTEGER PRIMARY KEY. Life will become
easier on all fronts.

Thing 3: If we really have no idea what your situation/problem is,
perhaps you should educate us so we can answer you better. If you ask
us something, but hide/obfuscate part of the problem, then it wastes
everyone's time.

This is a nice list. Let's keep it that way please.


>  a) implying I have no idea whatsoever on what I'm doing
>  b) suggesting me to "go read a book".
>
> These actions of yours are rude, not helping in any way and imply I'm
> a fool in what I'm doing and a fool to ask.
>
> On a more constructive side-note: you are completely wrong in your
> assumption that my primary key is "just Product". It isn't. Has never
> been and will never be. The primary key is as described simply just
> because "it's possible for two records to exist which have the same
> "Oper, Product, Category, Name" but different CreateTS."
>
>
> regards,
> Florian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[John Elrick]

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
> databases or SQL or SQLite in general or in detail so please(!) stop
>   

I hate to chime in here, but:

"I'm a starter on SQL / SQLite..."

Your exact words from your first posting.  So either:

a. You are a beginner in both SQL and SQLite, in which case the 
suggestion to obtain the extensive knowledge available from books is 
valid or
b. your statement mislead the group by misinforming them as to your 
state of knowledge.

FWIW


John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Jean-Denis Muys]

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 on
databases
> or SQL or SQLite in general or in detail so please(!) stop

 a) implying I
> have no idea whatsoever on what I'm doing
 b) suggesting me to "go read a
> book".

These actions of yours are rude, not helping in any way and imply
> I'm
a fool in what I'm doing and a fool to ask.

Well, if the kind people on this list took the time to answer a question
*you* asked while having no idea what you're supposed to do, maybe the wrong
is with *you*. What kind of business have you asking questions here and then
going after people because they don't have any idea what *you* are supposed
to do?

Why didn't it cross *your* mind that maybe *you* could have provided the
necessary background for *your* question?

Why didn't it cross *your* mind that if people give *you* suggestions to
read a beginners book, then maybe it's because *your* question didn't
demonstrate any knowledge that may let them infer otherwise?

So please come down from your high horses. People here are a lot more
knowledgeable than I am, and as far as I can see from *your* prose alone,
than you as well.

Jean-Denis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Florian Schricker]

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 read a boos on databases.  The shorter your primary key is, the faster 
> everything works.  You want a very short primary key.  There would be no 
> point in putting 'CreateTS' in your primary key unless it's possible for two 
> records to exist which have the same "Oper, Product, Category, Name" but 
> different CreateTS.
> I might guess that your primary key is probably just Product.  If it's not 
> possible to have two records with the same 'Product' then that is your 
> primary key.

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
databases or SQL or SQLite in general or in detail so please(!) stop

 a) implying I have no idea whatsoever on what I'm doing
 b) suggesting me to "go read a book".

These actions of yours are rude, not helping in any way and imply I'm
a fool in what I'm doing and a fool to ask.

On a more constructive side-note: you are completely wrong in your
assumption that my primary key is "just Product". It isn't. Has never
been and will never be. The primary key is as described simply just
because "it's possible for two records to exist which have the same
"Oper, Product, Category, Name" but different CreateTS."


regards,
Florian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Tim Romano]

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 the composite key 
and then use an aggregate max() function to find the latest (biggest) 
value for the timestamp column for each aggregation:

select oper, product, category, name, max(CreateTS) as LatestTimestamp
from yourTable
group by oper, product, category, name

The query above returns the rows that contain the most recent 
measurement but does not return the value itself.  To get your values, 
you can join your table again to the query above, representing the query 
above as an inline view ( a set of data or a "relation"):

select oper, product, category, name, value
from yourTable as T
JOIN
(
select oper, product, category, name, max(CreateTS)
from yourTable
group by oper, product, category, name
) as MostRecent

  on T.oper = MostRecent.oper
and  T.product=MostRecent.product
and T.category=MostRecent.category
and T.name=MostRecent.name
and T.CreateTS = MostRecent.LatestTimeStamp

order by T.oper, T.product, T.category, T.name

NOTE, that this approach assumes the data in column CreateTS is a string 
and always follows the format:

2009-11-24 09:49:20.25
-MM-DD HR:MN:SECONDS.HUNDREDTHS


Regards
Tim Romano



Florian Schricker wrote:
> 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 (string)
>  - Product (string)
>  - Category (string)
>  - Name (string)
>  - CreateTS (Timestamp)
>  - Value (Double)
>
> Primary keys are Oper, Product, Category, Name and CreateTS
>
> Basic usage is dumping "measurements" as doubles into the table. This
> happens usually in larger groups like
>
>  - "op", "cat", "product", "m1", 1.234, 2009-11-24 09:49:20.25
>  - "op", "cat", "product", "m2", 1.234, 2009-11-24 09:49:20.25
>  - "op", "cat", "product", "m3", 1.234, 2009-11-24 09:49:20.25
>  - "op", "cat", "product", "m4", 1.234, 2009-11-24 09:49:20.25
>
> Now suppose this has happend a lot so that m1 to m4 is present quite
> often but of course with differing timestamps in CreateTS (it's a
> primary key after all).
>
> What I could not figure out: Suppose I want to select rows by "op",
> "cat", "product" and get all measurements "m1" to "m4" with the latest
> timestamp in CreateTS.
>
> 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?
>
> Any help is very much appreciated! (I know this might be a simple SQL
> question for most here and I should better get some training on all
> that. But if somebody can help a bit with that I'd sure be glad!)
>
>
> kind regards
> Florian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.426 / Virus Database: 270.14.103/2558 - Release Date: 12/11/09 
> 10:06:00
>
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Simon Slavin]

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 a 
column type of TEXT) or as Unix epochs (which would be a column type of 
INTEGER).  Either way, all you need to be sure of is that the field sorts in a 
consistent manner (i.e. that INDEX or ORDER BY on it will work).

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Igor Tandetnik]

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 select rows by "op",
> "cat", "product" and get all measurements "m1" to "m4" with the latest
> timestamp in CreateTS.
> 
> So for a set of measurements identified by everything but their name
> select the latest rows as defined by the timestamp.

select * from mytable t1
where oper=:oper and product=:product and category=:category and
createTs = (select max(createTs) from mytable t2
where t1.oper = t2.oper and t1.product = t2.product and t1.category = 
t2.category and t1.name = t2.name
);

Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Rich Shepard]

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 one table or a set of tables.

> Primary keys are Oper, Product, Category, Name and CreateTS

   There is only one primary key per table.

> Can I do that in SQL in one query?

   I urge you to learn about database design and SQL. You can find a lot of
information on the Web and there are many good books on each topic. Only one
of the above attributes is specified as 'Double', yet you write about
dumping data as doubles into each one. Step back and do some learning before
you dig a big hole and fall into it.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Pavel Ivanov]

> 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'
order by CreateTS desc
limit 10;

Constant in the "limit" clause depends on how many "latest rows" do you need.


Pavel

On Fri, Dec 11, 2009 at 8:13 AM, Florian Schricker  wrote:
> 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 (string)
>  - Product (string)
>  - Category (string)
>  - Name (string)
>  - CreateTS (Timestamp)
>  - Value (Double)
>
> Primary keys are Oper, Product, Category, Name and CreateTS
>
> Basic usage is dumping "measurements" as doubles into the table. This
> happens usually in larger groups like
>
>  - "op", "cat", "product", "m1", 1.234, 2009-11-24 09:49:20.25
>  - "op", "cat", "product", "m2", 1.234, 2009-11-24 09:49:20.25
>  - "op", "cat", "product", "m3", 1.234, 2009-11-24 09:49:20.25
>  - "op", "cat", "product", "m4", 1.234, 2009-11-24 09:49:20.25
>
> Now suppose this has happend a lot so that m1 to m4 is present quite
> often but of course with differing timestamps in CreateTS (it's a
> primary key after all).
>
> What I could not figure out: Suppose I want to select rows by "op",
> "cat", "product" and get all measurements "m1" to "m4" with the latest
> timestamp in CreateTS.
>
> 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?
>
> Any help is very much appreciated! (I know this might be a simple SQL
> question for most here and I should better get some training on all
> that. But if somebody can help a bit with that I'd sure be glad!)
>
>
> kind regards
> Florian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL question

[Florian Schricker]

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 (string)
 - Product (string)
 - Category (string)
 - Name (string)
 - CreateTS (Timestamp)
 - Value (Double)

Primary keys are Oper, Product, Category, Name and CreateTS

Basic usage is dumping "measurements" as doubles into the table. This
happens usually in larger groups like

 - "op", "cat", "product", "m1", 1.234, 2009-11-24 09:49:20.25
 - "op", "cat", "product", "m2", 1.234, 2009-11-24 09:49:20.25
 - "op", "cat", "product", "m3", 1.234, 2009-11-24 09:49:20.25
 - "op", "cat", "product", "m4", 1.234, 2009-11-24 09:49:20.25

Now suppose this has happend a lot so that m1 to m4 is present quite
often but of course with differing timestamps in CreateTS (it's a
primary key after all).

What I could not figure out: Suppose I want to select rows by "op",
"cat", "product" and get all measurements "m1" to "m4" with the latest
timestamp in CreateTS.

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?

Any help is very much appreciated! (I know this might be a simple SQL
question for most here and I should better get some training on all
that. But if somebody can help a bit with that I'd sure be glad!)


kind regards
Florian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Dennis Volodomanov]

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, ID from Data where ID=?) myRow
> where PlotOnGraph=1 and d.Date <= myRow.Date and
>   (d.Date < myRow.Date or d.ID < myRow.ID);
>
>   

Ok, I'll investigate if I can do anything having that as a base - thank 
you. Unfortunately the results are sorted by the application (so, the 
grid control sorts them by date, but it doesn't take the ID in to 
account, so it's impossible to predict what the order is, as compared to 
the DB's sorted order). A bit of a mess...

   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

"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 Data
>> where PlotOnGraph=1
>> and Date < (select Date from Data where ID=?);
>>
>>
> This works well, until if hits a non-unique date (which happens often
> in this application). Is there a way to make it work with non-unique
> dates as well?

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, ID from Data where ID=?) myRow
where PlotOnGraph=1 and d.Date <= myRow.Date and
  (d.Date < myRow.Date or d.ID < myRow.ID);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Dennis Volodomanov]

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 well, until if hits a non-unique date (which happens often in 
this application). Is there a way to make it work with non-unique dates 
as well?

Thank you!

   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Dennis Volodomanov]

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 out the *position* in the returned result set of 
a Data with a given ID? So, let's say the returned result was (just IDs):

1   2   3   4   5   6   9   7   11   14   12   13   10

Can I find out that ID=9 is number 7 (or 6 if zero-based, doesn't 
matter) using only SQL?

Thanks in advance!

   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Dennis Volodomanov]

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
> LIMIT ?2 OFFSET ?1
>   

No real reason. I am porting older code and kept it "safe" (keeping old 
SQL unchanged for the time being). I'll change that later.

>> 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=?);
>   

Looks interesting - I'll try it later tonight!

Thank you for the help.

   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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 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=?);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Dennis Volodomanov]

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 out the *position* in the returned result set of 
a Data with a given ID? So, let's say the returned result was (just IDs):

1   2   3   4   5   6   9   7   11   14   12   13   10

Can I find out that ID=9 is number 7 (or 6 if zero-based, doesn't 
matter) using only SQL?

Thanks in advance!

  Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Lloyd]

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. R.
>
>
> 
>
> J. R. Westmoreland
>
> E-mail: j...@jrw.org
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



__
Scanned and protected by Email scanner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[J. R. Westmoreland]

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

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question with SQLite

[Jay A. Kreibich]

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 what WHERE is for.

  In fact, if you put all the limits in HAVING, you give the database
  no choice but to calculate a full cross-join on all the tables.
  HAVING cannot be used to limit the number of rows in a join.

> Are the two methods roughly equivalent in performance ?

  No.  For a given query, replacing WHERE with HAVING will usually
  result in a **significantly** longer run-time, especially if any
  joins are involved.  It will also require a great deal more
  resources.

> We ask this because we wonder why  using the GROUP BY + HAVING solution 
> with 7 tables instead of WHERE clauses we see big degradation of 
> perforamnce (0,5 seconds against 30 minutes)  + break with "disk full" 
> error message .

  The FROM and WHERE clauses are tightly bound.  The query optimizer
  understands how to fold WHERE limits into the FROM statement,
  rejecting many of the rows before they're joined.

  HAVING is used to post-process the GROUP BY result.  If all the
  limits are in a HAVING clause (and you have no explicit JOIN...ON
  limits), the system has no choice but to realize a full cross-join
  of all 7 tables, resulting in an absolutely enormous meta-table.

  For example, if the row count on your seven tables is 34, 104, 200,
  1742, 45, 2, and 15, the database will have to create a temporary 
  table with 1,663,122,240,000 rows (1.66 tera-rows).  Cross-joins add
  up VERY quickly.

  This is then processed by the GROUP BY (which is going to take a bit
  of time to chew through all that) and then (and only then) are rows
  rejected by the HAVING clause.


  HAVING exists to put limits on the output of GROUP BY.  For example,
  if you group sales by cities and only want to see cities with more
  than 100 sales, or some such thing.  In general, HAVING limits should
  only be acting on aggregate columns that are a result of the GROUP
  BY.  Any other limits or restrictions should go in the WHERE clause.
  You should almost never have a HAVING without a GROUP BY.

  It is completely acceptable (and appropriate) to have a WHERE,
  GROUP BY, and HAVING all in the same query.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question with SQLite

[Ken]

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
> To: sqlite-users@sqlite.org
> Date: Tuesday, December 9, 2008, 9:58 AM
> 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 wonder why  using the GROUP BY +
> HAVING solution 
> with 7 tables instead of WHERE clauses we see big
> degradation of 
> perforamnce (0,5 seconds against 30 minutes)  + break with
> "disk full" 
> error message . We checked the statement with GROUP BY +
> HAVING  against 
> the initial WHERE clause and are assured not to
> "miss" any  criteria 
> present in the WHERE clause.
> 
> Thanks
> 
> Jean-Marie
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL question with SQLite

[jm cuaz]

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 wonder why  using the GROUP BY + HAVING solution 
with 7 tables instead of WHERE clauses we see big degradation of 
perforamnce (0,5 seconds against 30 minutes)  + break with "disk full" 
error message . We checked the statement with GROUP BY + HAVING  against 
the initial WHERE clause and are assured not to "miss" any  criteria 
present in the WHERE clause.

Thanks

Jean-Marie


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Igor Tandetnik]

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 given token_id?
>
> E.g.:
>
> create table if not exists document_token
> (
> document_id integer not null,
> token_idinteger not null
> );
>
> create temporary table if not exists stage
> (
> id integer not null,
> );
>
> insert into stage( 11 );
> insert into stage( 12 );
>
> Given an input of 11 and 12 as token_id in the 'stage' table, what
> would be the proper query to retrieve document_id 1 and 2 (but not 3
> as it only contains token_id 11)?

select document_id
from document_token join stage on (token_id = id)
group by document_id
having count(*) = (select count(*) from stage);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL question

[Petite Abeille]

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 document_id which  
contains all the given token_id?

E.g.:

create table if not exists document_token
(
 document_id integer not null,
 token_idinteger not null
);

insert into document_token( 1, 11 );
insert into document_token( 1, 12 );

insert into document_token( 2, 11 );
insert into document_token( 2, 12 );

insert into document_token( 3, 11 );

create temporary table if not exists stage
(
 id integer not null,
);

insert into stage( 11 );
insert into stage( 12 );

Given an input of 11 and 12 as token_id in the 'stage' table, what  
would be the proper query to retrieve document_id 1 and 2 (but not 3  
as it only contains token_id 11)?

Simply joining the two tables will result in all the document_id being  
returned:

select  distinct
document_id
fromstage
joindocument_token on document_token.token_id = stage.id

Thoughts?

Thanks in advance.

Kind regards,

--
PA.
http://alt.textdrive.com/nanoki/













___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Gregor Brandt]

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


On 16-Jun-08, at 11:53 AM  , Darren Duncan wrote:

> 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 column_id = 2.  All that other  
> stuff you
> did just makes things unnecessarily more complicated, and possibly  
> buggy.
>
> On a related matter, UPDATE statements are atomic operations, so the  
> fact
> that the id is a primary key doesn't matter.  Since you're  
> incrementing all
> the id values simultaneously, there are no duplicate values at any  
> time, so
> the primary key constraint would remain happy.
>
> -- Darren Duncan
>
> Gregor Brandt 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 binary_report_fmt where column_id >= 3 order
>> by column_id desc);
>>
>> but it only updates the last item.   I guess I can make it a non-
>> primary key..then it works perfectly.
>>
>> Gregor
>>
>> On 16-Jun-08, at 10:04 AM  , [EMAIL PROTECTED] wrote:
>>
>>> update
>>> table
>>> set id = id + 1
>>> WHERE
>>> id > 1
>>>
>>> RBS
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-
Gregor Brandt

Marionette Software Group
Suite 202, 124 - 26 Ave SW
Calgary, AB, Canada T2S 3G5
Tel: +1 403 401 4784
EMail: [EMAIL PROTECTED]

www.marionette.ca





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Darren Duncan]

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 column_id = 2.  All that other stuff you 
did just makes things unnecessarily more complicated, and possibly buggy.

On a related matter, UPDATE statements are atomic operations, so the fact 
that the id is a primary key doesn't matter.  Since you're incrementing all 
the id values simultaneously, there are no duplicate values at any time, so 
the primary key constraint would remain happy.

-- Darren Duncan

Gregor Brandt 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 binary_report_fmt where column_id >= 3 order  
> by column_id desc);
> 
> but it only updates the last item.   I guess I can make it a non- 
> primary key..then it works perfectly.
> 
> Gregor
> 
> On 16-Jun-08, at 10:04 AM  , [EMAIL PROTECTED] wrote:
> 
>> update
>> table
>> set id = id + 1
>> WHERE
>> id > 1
>>
>> RBS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Kees Nuyt]

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 binary_report_fmt where column_id >= 3 order  
>by column_id desc);
>
>but it only updates the last item.   I guess I can make it a non- 
>primary key..then it works perfectly.
>
>Gregor

Add a large value to all column_id that have to be changed,
then subtract that same value minus 1.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Gregor Brandt]

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  
by column_id desc);

but it only updates the last item.   I guess I can make it a non- 
primary key..then it works perfectly.

Gregor

On 16-Jun-08, at 10:04 AM  , [EMAIL PROTECTED] wrote:

> 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, all entries  
>> with
>> id's >= the id need to be incremented to keep the id's unique
>>
>> ie
>>
>> 1 bob
>> 2 joe
>> 3 irene
>>
>> someone wants to insert 2 sarah, I need the table to look like this
>>
>> 1 bob
>> 2 sarah
>> 3 joe
>> 4 irene
>>
>>
>> Any help would be great.
>> thanks
>>
>> -
>> Gregor
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[bartsmissaert]

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, all entries with
> id's >= the id need to be incremented to keep the id's unique
>
> ie
>
> 1 bob
> 2 joe
> 3 irene
>
> someone wants to insert 2 sarah, I need the table to look like this
>
> 1 bob
> 2 sarah
> 3 joe
> 4 irene
>
>
> Any help would be great.
> thanks
>
> -
> Gregor
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[P Kishor]

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 an id, all entries with
>  id's >= the id need to be incremented to keep the id's unique
>
>  ie
>
>  1 bob
>  2 joe
>  3 irene
>
>  someone wants to insert 2 sarah, I need the table to look like this

-- to keep track of the id that is going to be duplicated
UPDATE table
SET id = -1
WHERE id = 2


INSERT INTO table VALUES (2, 'sarah');

>
>  1 bob
>  2 sarah
>  3 joe
>  4 irene
>

UPDATE table
SET id = id + 1
WHERE id > 2 OR id = -1



>
>  Any help would be great.
>  thanks
>
>  -
>  Gregor
>
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL question

[Gregor Brandt]

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 incremented to keep the id's unique

ie

1 bob
2 joe
3 irene

someone wants to insert 2 sarah, I need the table to look like this

1 bob
2 sarah
3 joe
4 irene


Any help would be great.
thanks

-
Gregor


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Andrea Galligani]

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, info text );
> sqlite> insert into tst values( 'A', 289, 'A1' );
> sqlite> insert into tst values( 'C', 29, 'C1' );
> sqlite> insert into tst values( 'A', 29, 'A2' );
> sqlite> insert into tst values( 'C', 129, 'C2' );
> sqlite> insert into tst values( 'C', 19, 'C3' );
> sqlite> insert into tst values( 'A', 1129, 'A3' );
> sqlite> insert into tst values( 'B', 19, 'B1' );
> sqlite> insert into tst values( 'A', 19, 'A4' );
> sqlite> insert into tst values( 'B', 9, 'B2' );
> sqlite> insert into tst values( 'B', 99, 'B3' );
> sqlite>
> sqlite> select * fro tst;
> SQL error: near "fro": syntax error
> sqlite> select * from tst;
> A|289|A1
> C|29|C1
> A|29|A2
> C|129|C2
> C|19|C3
> A|1129|A3
> B|19|B1
> A|19|A4
> B|9|B2
> B|99|B3
> sqlite> select tst.* from tst cross join
>  ...> ( select max(score) as maxS, name from tst group by name ) as 
> subQuery
>  ...> on tst.name=subQuery.name and tst.score = subQuery.maxS;
> C|129|C2
> A|1129|A3
> B|99|B3
> sqlite>
>
>
> Rgds,
> Simon
>
>   
-- 
--
Andrea Galligani

Macs Tech s.r.l.
Via San Paolo 11, 56125
Pisa - Italy

Phone...: +39 050 40915
e-mail..: [EMAIL PROTECTED]
--

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Federico Granata]

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
Geekness]
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).

On Fri, Jun 6, 2008 at 11:03 AM, Andrea Galligani <
[EMAIL PROTECTED]> wrote:

> Hi to all,
>
> I'm a novice in SQL and SQLite so I apologize if this question has an
> obvious solution
>
> I have a table formed in this way.
>
> NAME   SCORE   INFO1   INFO2   etc.
> Andrew   5 aaa bbb ...
> Andrew   8 ddd eee ...
> Paul 4 xxx yyy ...
> Paul 6 aaa fff ...
>
> I need a query to get from any name the row with the max score. So from
> the above sample, I would like to obtain the following rows.
>
> NAME   SCORE   INFO1   INFO2   etc.
> Andrew   8 ddd eee ...
> Paul 6 aaa fff ...
>
> Can I get this result using a complex query or should I filter the rows
> using any different tool from SQL (C/C++)?
>
> Thanks in advance
> Andrea
>
> --
> --
> Andrea Galligani
>
> Macs Tech s.r.l.
> Via San Paolo 11, 56125
> Pisa - Italy
>
> Phone...: +39 050 40915
> e-mail..: [EMAIL PROTECTED]
> --
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Simon Davies]

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( 'A', 29, 'A2' );
sqlite> insert into tst values( 'C', 129, 'C2' );
sqlite> insert into tst values( 'C', 19, 'C3' );
sqlite> insert into tst values( 'A', 1129, 'A3' );
sqlite> insert into tst values( 'B', 19, 'B1' );
sqlite> insert into tst values( 'A', 19, 'A4' );
sqlite> insert into tst values( 'B', 9, 'B2' );
sqlite> insert into tst values( 'B', 99, 'B3' );
sqlite>
sqlite> select * fro tst;
SQL error: near "fro": syntax error
sqlite> select * from tst;
A|289|A1
C|29|C1
A|29|A2
C|129|C2
C|19|C3
A|1129|A3
B|19|B1
A|19|A4
B|9|B2
B|99|B3
sqlite> select tst.* from tst cross join
 ...> ( select max(score) as maxS, name from tst group by name ) as subQuery
 ...> on tst.name=subQuery.name and tst.score = subQuery.maxS;
C|129|C2
A|1129|A3
B|99|B3
sqlite>


Rgds,
Simon

2008/6/6 Andrea Galligani <[EMAIL PROTECTED]>:
> Hi to all,
>
> I'm a novice in SQL and SQLite so I apologize if this question has an
> obvious solution
>
> I have a table formed in this way.
>
> NAME   SCORE   INFO1   INFO2   etc.
> Andrew   5 aaa bbb ...
> Andrew   8 ddd eee ...
> Paul 4 xxx yyy ...
> Paul 6 aaa fff ...
>
> I need a query to get from any name the row with the max score. So from
> the above sample, I would like to obtain the following rows.
>
> NAME   SCORE   INFO1   INFO2   etc.
> Andrew   8 ddd eee ...
> Paul 6 aaa fff ...
>
> Can I get this result using a complex query or should I filter the rows
> using any different tool from SQL (C/C++)?
>
> Thanks in advance
> Andrea
>
> --
> --
> Andrea Galligani
>
> Macs Tech s.r.l.
> Via San Paolo 11, 56125
> Pisa - Italy
>
> Phone...: +39 050 40915
> e-mail..: [EMAIL PROTECTED]
> --
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL question regarding triggers updating values

[Dennis Volodomanov]

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 them around with either next
or previous values), effectively moving them up or down an ordered list.

 

I am thinking about using an UPDATE trigger (so, I update one of
FileOrders to become the next one, and the trigger should update the
next one to become the current one) - would that be the best solution if
I was to try to do it using only SQL? Of course I can do it in the
program, but I think that doing it inside SQLite would be a bit faster?
Is there any other way to do this using SQL?

 

Thanks in advance for your ideas/suggestions :)

 

Regards,

 

   Dennis

 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sql question

[Dennis Cote]

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/16175/match=fifo


HTH
Dennis Cote

[sqlite] sql question

[Uma Venkataraman]

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

[Veysel Harun Sahin]

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 to select records from the table by grouping with id field. I use
groupby for this. But i also want to see all the number fields' datas in a
group side by side. I have written down the list which i want to see after
the query.

First record - id: 1, name: John, number: 54
Second record - id: 2, name Richard, number: 1

Is there any idea about how to write a query to get this result?

Thanks in advace.

--
Veysel Harun Sahin

Re: [sqlite] SQL question

[Lloyd Dupont]

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 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 TABLE Ingredients(
   ID INTEGER PRIMARY KEY,
   name TEXT,
   description BLOB,
   property_ID INTEGER
);

CREATE TABLE Properties(
   ID INTEGER PRIMARY KEY,
   price double,
   calories double
);

CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients
BEGIN
   INSERT INTO Properties (price) VALUES (NULL);
   UPDATE Ingredients
   SET property_ID = last_insert_rowid()
   WHERE ID=OLD.ID;
END;

--
it looks like OLD is now the newly inserted line in Properties and not the 
on in Ingredients ?!?!

is it a bug or a feature?
how to work around it?

Re: [sqlite] SQL question

[Lloyd Dupont]

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 TABLE Ingredients(
   ID INTEGER PRIMARY KEY,
   name TEXT,
   description BLOB,
   property_ID INTEGER
);

CREATE TABLE Properties(
   ID INTEGER PRIMARY KEY,
   price double,
   calories double
);

CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients
BEGIN
   INSERT INTO Properties (price) VALUES (NULL);
   UPDATE Ingredients
   SET property_ID = last_insert_rowid()
   WHERE ID=OLD.ID;
END;

--
it looks like OLD is now the newly inserted line in Properties and not the 
on in Ingredients ?!?!

is it a bug or a feature?
how to work around it?

Re: [sqlite] SQL question

[Derrell . Lipman]

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 Ingredients happen to 
> have the same number of rows?
>
> Martin
>
> Lloyd Dupont schrieb:
>
>> 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
>>WHERE ID=OLD.ID;
>> END;

According to the documentation, you should be able to use the
last_insert_rowid() function:

CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients
BEGIN
   INSERT INTO Properties (price) VALUES (NULL);
   UPDATE Ingredients
   SET property_ID = last_insert_rowid()
   WHERE ID=OLD.ID;
END;

Derrell

Re: [sqlite] SQL question

[Martin Engelschalk]

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?


Martin

Lloyd Dupont schrieb:


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
   WHERE ID=OLD.ID;
END;


-- price doesn't matter. I set it up much later.. (whenever the user 
want, in fact), but I want my property line created and correctly linked!

Re: [sqlite] SQL question

[Lloyd Dupont]

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
   WHERE ID=OLD.ID;
END;


-- price doesn't matter. I set it up much later.. (whenever the user want, 
in fact), but I want my property line created and correctly linked!

Re: [sqlite] SQL question

[Martin Engelschalk]

Hi,

i am not sure if i understand your problem correctly. Perhaps the 
following does it:


CREATE TRIGGER create_ingredients_property AFTER INSERT ON Ingredients
FOR EACH ROW
BEGIN
  INSERT INTO Properties (price) VALUES ();
  UPDATE Ingredients SET property_ID (select max(id) from properties) 
WHERE ID=OLD.ID;

END;

Because the id is omitted in the Insert - Statement to 'properties', 
sqlite supplies one, which you can then update into ingredients.

However, I'm not clear where the price is supposed to come from.

Martin

Lloyd Dupont schrieb:


BTW, one more question / precision.
in INSERT INTO Properties() I didn't pass the value for ID.
because basically I want an auto-incremented value which I don't have to
worry about.
maybe that's not the way to use such value ?!


- Original Message - From: "Lloyd Dupont" <[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(
   ID INTEGER PRIMARY KEY,
   price double
);

When I create a new Ingredient I would like to create a new property 
for this ingredient and setup its property_ID.


because there is no stored procedure I was thinking to use a trigger 
like this one:

CREATE TRIGGER create_ingredients_property AFTER INSERT ON Ingredients
FOR EACH ROW
BEGIN
   INSERT INTO Properties () VALUES ();
   UPDATE Ingredients SET property_ID=ROWID WHERE ID=OLD.ID;
END;

but it doesn't work
I could do with some help for the SQL syntax :D 

Re: [sqlite] SQL question

[Lloyd Dupont]

BTW, one more question / precision.
in INSERT INTO Properties() I didn't pass the value for ID.
because basically I want an auto-incremented value which I don't have to
worry about.
maybe that's not the way to use such value ?!


- Original Message - 
From: "Lloyd Dupont" <[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(
   ID INTEGER PRIMARY KEY,
   price double
);

When I create a new Ingredient I would like to create a new property for 
this ingredient and setup its property_ID.


because there is no stored procedure I was thinking to use a trigger like 
this one:

CREATE TRIGGER create_ingredients_property AFTER INSERT ON Ingredients
FOR EACH ROW
BEGIN
   INSERT INTO Properties () VALUES ();
   UPDATE Ingredients SET property_ID=ROWID WHERE ID=OLD.ID;
END;

but it doesn't work
I could do with some help for the SQL syntax :D 

[sqlite] SQL question

[Lloyd Dupont]

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 
ingredient and setup its property_ID.

because there is no stored procedure I was thinking to use a trigger like this 
one:
CREATE TRIGGER create_ingredients_property AFTER INSERT ON Ingredients
FOR EACH ROW
BEGIN
INSERT INTO Properties () VALUES ();
UPDATE Ingredients SET property_ID=ROWID WHERE ID=OLD.ID;
END;

but it doesn't work
I could do with some help for the SQL syntax :D

Re: [sqlite] SQL Question

[Brass Tilde]

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

select  team1.name, team2.name, games.date, games.result
fromgames
inner join teams team1 on games.team1_id = team1.id
inner join teams team2 on games.team2_id = team2.id;

> 2. What query would be best if I want to search for games that
> two specific teams had played. For example, I want all the games
> that the teams with the names 'bla' and 'blub' played against
> each other. Is there any way to do that?

select  team1.name, team2.name, games.date, games.result
fromgames
inner join teams team1 on games.team1_id = team1.id
and team1.name in ('bla', 'blub')
inner join teams team2 on games.team2_id = team2.id
and team2.name in ('bla', 'blub');


I didn't check spelling or stuff, but the syntax should be mostly correct.

[sqlite] SQL Question

[Gilbert Jeiziner]

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

2. What query would be best if I want to search for games that
two specific teams had played. For example, I want all the games
that the teams with the names 'bla' and 'blub' played against
each other. Is there any way to do that?

I tried this with joins, with groupings, but wasn't able to get
the lists i wanted. I can get the name of one team, but not the
name of the other team.

Any help would be appreciated

Gilbert