Re: [sqlite] Can SQLite do this in a single query?

[Keith Medcalf]

Well, "paint" is to draw your output.  So, for example, you can output an 
arbitrary matrix like this:

select columnHeader from theColumns order by columnHeader;

from which you can output ""
then for each row retrieved output "" columnHeader ""
and when you run out of rows
""
and keep track of how many rows you read.

Then select your data and paint it

select data from 

and if you are currently on column 0 output ""
then output "" data ""
and if you just output the last column reset your internal column counter to 0 
and output ""
and when you run out of stuff to output, then write ""

voila -- you just "painted an html table"


-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Winfried
>Sent: Thursday, 24 October, 2019 17:11
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Can SQLite do this in a single query?
>
>Simon Slavin-3 wrote
>> https://sqlite.org/books.html
>
>Thanks.
>
>"You have to paint the output table yourself." "You still have to "paint
>the
>fence" yourself, though now the table data is not sparse, thus easier to
>paint"
>
>Looks like "paint" is DB lingo: Does it mean formatting the output after
>running the right SELECT ?
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>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] Can SQLite do this in a single query?

[Winfried]

Simon Slavin-3 wrote
> https://sqlite.org/books.html

Thanks.

"You have to paint the output table yourself." "You still have to "paint the
fence" yourself, though now the table data is not sparse, thus easier to
paint"

Looks like "paint" is DB lingo: Does it mean formatting the output after
running the right SELECT ?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing `SELECT a, max(b) GROUP BY a`

[Simon Slavin]

On 24 Oct 2019, at 7:38pm, Jens Alfke  wrote:

> I've created an index on `name, timestamp desc`.

Make another one missing out the DESC.  Just in case it helps.  You'll probably 
just delete it after a little testing.

I don't see anything obviously wrong with what you wrote.

> One simplifying assumption: I basically never drop any rows, and if I do 
> they're old rows, so max(timestamp) for any author will only increase.

Well, timestamps can only increase.  So if you never delete the row containing 
the latest update for each name …

Depending how often you run the query you posted about, had you considered 
creating another table which just contains name and latest timestamp and value 
?  You could update that table inside your code, or use a TRIGGER on INSERT 
into the other table.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing `SELECT a, max(b) GROUP BY a`

[Keith Medcalf]

It appears that the optimizer will not utilize a skip-scan *AND* apply the max 
optimization concurrently.

select name, 
   (
select max(timestamp) 
  from table 
 where name=outer.name
   ) 
  from (
select distinct name
  from table
   );

The above query uses the skip-scan optimization to get the distinct "name", and 
then for each distinct "name" then uses a max optimized query to get the 
max(timestamp) value.

You need to have an index on (name, timestamp).  The max() optimization appears 
to get applied when the index is on timestamp desc even though in that case if 
may have to "scan past" nulls.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Jens Alfke
>Sent: Thursday, 24 October, 2019 12:38
>To: SQLite mailing list 
>Subject: [sqlite] Optimizing `SELECT a, max(b) GROUP BY a`
>
>I'm looking for an optimal way to query the maximum value of one
>(integer) column as grouped by another (string) column. For example, say
>I've got a table of timestamped data points from many named sources, and
>I want to find each source's latest timestamp. I'm seeing decent
>performance if the database is hot in the filesystem cache, but not when
>it's cold — worst case this query takes about 7sec to run on a 2018
>MacBook Pro with a decent SSD.
>
>My table schema includes something like `… name text, timestamp integer
>…`.
>The table has about 500,000 rows, with 1700 distinct `name` values, and
>occupies about 500MB on disk.
>I'm querying `SELECT name, max(timestamp) FROM table GROUP BY name`.
>I've created an index on `name, timestamp desc`.
>
>EXPLAIN QUERY PLAN says:
>   7|0|0| SCAN TABLE table USING INDEX namesAndTimestamps
>Note the dreaded `SCAN`. :-(
>
>I think I understand what's going on here: even if SQLite can tell it
>only needs to use the first index entry for any given name (because that
>will have the largest timestamp), it still has to scan through the index
>to find where the next name starts. (Or is there some optimization to
>jump rapidly through the B-tree to do this?)
>
>Is there anything I can do to the schema, index or query to make this
>more efficient?
>
>One simplifying assumption: I basically never drop any rows, and if I do
>they're old rows, so max(timestamp) for any author will only increase.
>
>Thanks for any advice…
>
>—Jens
>___
>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

[sqlite] Optimizing `SELECT a, max(b) GROUP BY a`

[Jens Alfke]

I'm looking for an optimal way to query the maximum value of one (integer) 
column as grouped by another (string) column. For example, say I've got a table 
of timestamped data points from many named sources, and I want to find each 
source's latest timestamp. I'm seeing decent performance if the database is hot 
in the filesystem cache, but not when it's cold — worst case this query takes 
about 7sec to run on a 2018 MacBook Pro with a decent SSD.

My table schema includes something like `… name text, timestamp integer …`.
The table has about 500,000 rows, with 1700 distinct `name` values, and 
occupies about 500MB on disk.
I'm querying `SELECT name, max(timestamp) FROM table GROUP BY name`.
I've created an index on `name, timestamp desc`.

EXPLAIN QUERY PLAN says:
7|0|0| SCAN TABLE table USING INDEX namesAndTimestamps
Note the dreaded `SCAN`. :-(

I think I understand what's going on here: even if SQLite can tell it only 
needs to use the first index entry for any given name (because that will have 
the largest timestamp), it still has to scan through the index to find where 
the next name starts. (Or is there some optimization to jump rapidly through 
the B-tree to do this?)

Is there anything I can do to the schema, index or query to make this more 
efficient?

One simplifying assumption: I basically never drop any rows, and if I do 
they're old rows, so max(timestamp) for any author will only increase.

Thanks for any advice…

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

Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll

[Joe Mistachkin]

Yes, the NuGet packages are included in the release.

Sent from my iPhone

> On Oct 24, 2019, at 9:13 AM, Keith Bertram  wrote:
> 
> Ok. Thanks. Does it get immediately placed under nuget?
> 
> Keith
> 
> -Original Message-
> From: sqlite-users  On Behalf 
> Of Joe Mistachkin
> Sent: Thursday, October 24, 2019 8:12 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll
> 
> 
> Within the next week or so.
> 
> Sent from my iPhone
> 
>> On Oct 24, 2019, at 8:37 AM, Keith Bertram  wrote:
>> 
>> Ok. Thanks. Do you have an estimated release date?
>> 
>> Keith
>> 
>> -Original Message-
>> From: sqlite-users  On 
>> Behalf Of Joe Mistachkin
>> Sent: Wednesday, October 23, 2019 9:08 PM
>> To: 'SQLite mailing list' 
>> Subject: Re: [sqlite] sqlite3_limit equivalent in 
>> System.Data.SQLite.dll
>> 
>> 
>> Keith Bertram wrote:
>>> 
>>> The default limit for the number of attached databases appears to be
>>> 10
>> with the
>>> 1.0.106 version of System.Data.SQLite.dll and I would like to 
>>> increase
>> this value
>>> to 25. I've used the sqlite3_limit function in the past to increase 
>>> this

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

Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll

[Keith Bertram]

Ok. Thanks. Does it get immediately placed under nuget?

Keith

-Original Message-
From: sqlite-users  On Behalf Of 
Joe Mistachkin
Sent: Thursday, October 24, 2019 8:12 AM
To: SQLite mailing list 
Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll


Within the next week or so.

Sent from my iPhone

> On Oct 24, 2019, at 8:37 AM, Keith Bertram  wrote:
> 
> Ok. Thanks. Do you have an estimated release date?
> 
> Keith
> 
> -Original Message-
> From: sqlite-users  On 
> Behalf Of Joe Mistachkin
> Sent: Wednesday, October 23, 2019 9:08 PM
> To: 'SQLite mailing list' 
> Subject: Re: [sqlite] sqlite3_limit equivalent in 
> System.Data.SQLite.dll
> 
> 
> Keith Bertram wrote:
>> 
>> The default limit for the number of attached databases appears to be
>> 10
> with the
>> 1.0.106 version of System.Data.SQLite.dll and I would like to 
>> increase
> this value
>> to 25. I've used the sqlite3_limit function in the past to increase 
>> this
> value
>> but I don't see a matching way to do this within System.Data.SQLite.dll.
>> 
> 
> Thanks for pointing this out.  I'll make sure that some way to accomplish 
> this ends up in the 1.0.112.0 release, which is due out shortly.
> 
> --
> Joe Mistachkin
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlit
> e.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=hmGTLOph1qd
> _VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4T
> sA-hoNTc=m2M1vgITLsaAq32oeWR3t3BL5zfQYJkiHqNZ17uZtSk=sCLCkHRvR-ztd
> vVVSGxqaiz1a_tcrqNlHTio4zORCuE= 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlit
> e.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=hmGTLOph1qd
> _VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4T
> sA-hoNTc=ToaKjNz0BYd7qdR5qRjM5upVh5PLjdoYkQe5hWOHkGQ=MRsMclki0g0JT
> pTyrcS2EvjvvP5z5dOAS2yXbUYta8c=
> 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=hmGTLOph1qd_VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4TsA-hoNTc=ToaKjNz0BYd7qdR5qRjM5upVh5PLjdoYkQe5hWOHkGQ=MRsMclki0g0JTpTyrcS2EvjvvP5z5dOAS2yXbUYta8c=
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll

[Joe Mistachkin]

Within the next week or so.

Sent from my iPhone

> On Oct 24, 2019, at 8:37 AM, Keith Bertram  wrote:
> 
> Ok. Thanks. Do you have an estimated release date?
> 
> Keith
> 
> -Original Message-
> From: sqlite-users  On Behalf 
> Of Joe Mistachkin
> Sent: Wednesday, October 23, 2019 9:08 PM
> To: 'SQLite mailing list' 
> Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll
> 
> 
> Keith Bertram wrote:
>> 
>> The default limit for the number of attached databases appears to be 
>> 10
> with the
>> 1.0.106 version of System.Data.SQLite.dll and I would like to increase
> this value
>> to 25. I've used the sqlite3_limit function in the past to increase 
>> this
> value
>> but I don't see a matching way to do this within System.Data.SQLite.dll.
>> 
> 
> Thanks for pointing this out.  I'll make sure that some way to accomplish 
> this ends up in the 1.0.112.0 release, which is due out shortly.
> 
> --
> Joe Mistachkin
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=hmGTLOph1qd_VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4TsA-hoNTc=m2M1vgITLsaAq32oeWR3t3BL5zfQYJkiHqNZ17uZtSk=sCLCkHRvR-ztdvVVSGxqaiz1a_tcrqNlHTio4zORCuE=
>  
> ___
> 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] sqlite3_limit equivalent in System.Data.SQLite.dll

[Keith Bertram]

Ok. Thanks. Do you have an estimated release date?

Keith

-Original Message-
From: sqlite-users  On Behalf Of 
Joe Mistachkin
Sent: Wednesday, October 23, 2019 9:08 PM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll


Keith Bertram wrote:
>
> The default limit for the number of attached databases appears to be 
> 10
with the
> 1.0.106 version of System.Data.SQLite.dll and I would like to increase
this value
> to 25. I've used the sqlite3_limit function in the past to increase 
> this
value
> but I don't see a matching way to do this within System.Data.SQLite.dll.
>

Thanks for pointing this out.  I'll make sure that some way to accomplish this 
ends up in the 1.0.112.0 release, which is due out shortly.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=hmGTLOph1qd_VnCqj81HzEWkDaxmYdIWRBdoFggzhj8=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4TsA-hoNTc=m2M1vgITLsaAq32oeWR3t3BL5zfQYJkiHqNZ17uZtSk=sCLCkHRvR-ztdvVVSGxqaiz1a_tcrqNlHTio4zORCuE=
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Simon Slavin]

On 24 Oct 2019, at 10:18am, Winfried  wrote:

> Is there a good book you would recommend to learn about SQLite, especially 
> regarding SELECT?

A list already made:



Books long and short, concentrating on different things.

You might also be interested in books about SQL, the query language used by 
SQLite and many other database engines.  This would include various complicated 
things that can be done using SELECT.  Unfortunately I don't learn well from 
books, so I can't recommend any.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Winfried]

Thank you very much to all. There's no way I could have come up with those
queries.

Considering the wealth/complexity of SELECT even in SQLite, it seems a whole
book could be written on just that command.

https://www.sqlite.org/images/syntax/select-stmt.gif

Is there a good book you would recommend to learn about SQLite, especially
regarding SELECT?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users