Re: [sqlite] LEFT JOIN: ON vs. WHERE

The difference between these two is what happens when a row of A has no matches in B. select * from A left join B on A.Time = B.Time select * from A left join B where A.Time = B.Time In the first one the condition is carried out during the join so if a row of A has no matches in B then the B

Re: [sqlite] Standard deviation last x entries

the range rule) although the approximation is not very good. On Fri, Oct 18, 2019 at 6:41 PM Gabor Grothendieck wrote: > > There is a stdev function for sqlite here: > https://www.sqlite.org/contrib//download/extension-functions.c?get=25 > > On Wed, Oct 16, 2019 at 7:57 PM Olaf

Re: [sqlite] Standard deviation last x entries

There is a stdev function for sqlite here: https://www.sqlite.org/contrib//download/extension-functions.c?get=25 On Wed, Oct 16, 2019 at 7:57 PM Olaf Schmidt wrote: > > Am 12.10.2019 um 16:47 schrieb Bart Smissaert: > > Sorry, I forgot to tell that. It is date column with an integer number. > >

Re: [sqlite] dates, times and R

Thanks for the idea but the interface already handles that and does it without special names. The last example in my last post shows that d was correctly typed in the output because the interface noticed that it had the same name as an input column. Other problems are that it would still not

Re: [sqlite] dates, times and R

lumn so we # convert it manually out <- sqldf("select d, d + 1 as nextDay from DF") out$nextDay <- as.Date(out$nextDay, origin = "1970-01-01") out ##dnextDay ## 1 2000-01-01 2000-01-02 On Sun, Aug 11, 2019 at 7:16 PM Keith Medcalf wrote: >

Re: [sqlite] dates, times and R

at 10:52 AM Simon Slavin wrote: > > On 11 Aug 2019, at 2:45pm, Gabor Grothendieck wrote: > > > R supports Date and POSIXct (date/time) classes which are represented > > internally as days and seconds since the UNIX Epoch respectively; > > however, due to the class it know

[sqlite] dates, times and R

It's really useful that SQLite now supports window operations as that was one of the key features that R users needed to do many manipulations using SQLite. From the perspective of the R language there is really one particularly key feature left that prevents some users from easily using SQLite

Re: [sqlite] Simple way to import GPX file?

The csvfix command line utility is useful to edit such files down to a csv file which can then be read into sqlite. csvfix from_xml -smq -re wpt gpx.xml On Sun, Dec 9, 2018 at 4:44 PM Winfried wrote: > > Hello, > > I need to importe a GPX file that contains a few thousand waypoints, eg. >

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

> It is a standard feature of pysqlite2 (the sqlite3 library shipped with > Python). I am quite sure you can read the documentation just as well as I > can copy and paste it. In short, you can use anything you like as the data > type in sqlite. You could specify that some column contains: >

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

On Mon, Feb 1, 2016 at 8:27 AM, Keith Medcalf wrote: > >> > But you already have pandas.read_sql_query. While that function >> > isn't really what I'd call simple, the complexity afaict -- dates, >> > floats, and chunks -- can be laid at Python's feet. >> >> I use R rather than python but the

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

On Sun, Jan 31, 2016 at 6:25 PM, James K. Lowden wrote: > On Sat, 30 Jan 2016 20:50:17 -0500 > Jim Callahan wrote: > But you already have pandas.read_sql_query. While that function > isn't really what I'd call simple, the complexity afaict -- dates, > floats, and chunks -- can be laid at

[sqlite] Best way to store only date

I frequently have to deal with dates coming from R's "Date" class which stores dates as the number of days since the UNIX epoch. So if x is the number of days since 1970-01-01 then this gives the -mm-dd representation of the date date(x + 2440588) and (annoyingly owing to the need for

[sqlite] Parsing the contents of a field

If it's OK to use the sqlite3 cmd line shell then try this: -- create test input table X create table X (what text); insert into X values ('abc,def'); -- write X to a file .output mydata.csv select * from X; .output stdout -- read it back in to parse it create table Y (a text, b text); .mode

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

This is how R works too. That is the RSQLite package that gives access to SQLite includes SQLite itself right in the package itself so one need not separately install SQLite. Also RSQlite uses the R DBI package which defines connections as classes which are subclassed by the various database

[sqlite] SQLite Extensions

Can you include in the distribution the precise code (make file or .bat file, etc.) used to build these. Thanks. On Fri, Nov 20, 2015 at 12:42 AM, Keith Medcalf wrote: > > For anyone who is interested, I have compiled for 32-bit Windows all the > extensions that are included in the SQLite3

[sqlite] Is it legal SQL to select a specific row of a group via a HAVING clause?

On Tue, Sep 29, 2015 at 6:16 AM, Simon Slavin wrote: > I don't like using sub-SELECT and I would love to see another reader > rephrase this using 'WITH' or a VIEW. > with sub as (select currency, price, max(day) from prices group by currency) select currency, price from sub;

[sqlite] CSV excel import

Here is how to do it in R. Download, install and start R and then paste the following code into R. Uncomment the first line (the line starting with a hash) if you don't already have sqldf installed. This not only installs sqldf but also the RSQLite driver and SQLite itself. The code assumes

[sqlite] Thanks SQLite

Both the solutions transformed the correlated subquery into a join prior to forming the CTE. Can we conclude, in general, that CTEs do not support correlated subqueries? On Fri, Jul 31, 2015 at 11:30 AM, R.Smith wrote: > > > On 2015-07-31 03:40 PM, Gabor Grothendieck wrote: > >&

[sqlite] Thanks SQLite

On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin wrote: > > I am noting an overlap with the things SQLite users have been using > sub-SELECTs for. > > Here is a self contained example that can be fed into the sqlite3 command line tool. It uses WITH to factor out the subquery; however, the annoying

[sqlite] PhD student

On Wed, Feb 25, 2015 at 11:28 AM, VASILEIOU Eleftheria wrote: > I would need to use R for my analysis for my Project and my supervisor > suggested me to learn the SQL language for R. > Could you please provide me some resources for learning SQL and R? Assuming you are looking to use SQL to work

[sqlite] Appropriate Uses For SQLite

On Wed, Feb 18, 2015 at 9:53 AM, Richard Hipp wrote: > On 2/18/15, Jim Callahan wrote: >> I would mention the open source statistical language R in the "data >> analysis" section. > > I've heard of R but never tried to use it myself. Is an SQLite > interface built into R, sure enough? Or is

Re: [sqlite] Whish List for 2015

On Sun, Dec 21, 2014 at 4:47 AM, big stone wrote: > Hi all, > > To prepare for end of 2014 greetings moment, here is my whish list for > 2015: > - a minimal subset of analytic functions [1], that I hope may help > end-user/students popularity [2] > - better information on

Re: [sqlite] What if OpenDocument were built atop SQLite?

On Wed, Sep 10, 2014 at 8:36 AM, John McKown wrote: > Well, I did a git clone to fetch the LibreOffice source. It appears to > be a mixture of Java, C, and C++. Just some stats: > > $find . -name '*.c' | wc > 108 1083908 >

Re: [sqlite] Window functions?

The wording in the cited link is that "Queries of the form: "SELECT max(x), y FROM table" returns the value of y on the same row that contains the maximum x value." There is some question of whether min(x) is "of the form" max(x). On Thu, Aug 28, 2014 at 10:28 AM, Clemens Ladisch

Re: [sqlite] RPAD/LPAD

On Thu, Mar 6, 2014 at 8:41 PM, RSmith <rsm...@rsweb.co.za> wrote: > > On 2014/03/07 01:59, Gabor Grothendieck wrote: >> >> >>> >>>> >>>>> A small enhancement request: >>>>> >>>>> It would be great if the RPA

Re: [sqlite] RPAD/LPAD

On Thu, Mar 6, 2014 at 6:29 PM, Walter Hurry wrote: > Richard Hipp wrote: > >> On Thu, Mar 6, 2014 at 3:41 PM, Walter Hurry wrote: >> >>> A small enhancement request: >>> >>> It would be great if the RPAD and LPAD functions could be implemented in

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

On Fri, Feb 14, 2014 at 2:33 PM, Max Vlasov wrote: > Hi, > > Some time ago when there was no "instr" functions, I looked at Mysql help > pages and implemented a user function "locate" as the one that allows > searching starting a particular position in the string. With two

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

On Mon, Feb 10, 2014 at 1:56 PM, Petite Abeille <petite.abei...@gmail.com> wrote: > > On Feb 10, 2014, at 7:39 PM, Gabor Grothendieck <ggrothendi...@gmail.com> > wrote: > >> That should have read right join. > > My personal opinion? Anyone even conside

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

nice to know that I will have rfc4180 when its upgraded. On Mon, Feb 10, 2014 at 1:26 PM, Petite Abeille <petite.abei...@gmail.com> wrote: > > On Feb 10, 2014, at 5:19 PM, Gabor Grothendieck <ggrothendi...@gmail.com> > wrote: > >> The other features that would ma

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

On Mon, Feb 10, 2014 at 10:23 AM, Richard Hipp wrote: > The Problem: > > Many new users (especially university students taking a "database 101" The other features that would make teaching a bit easier would be to support left join explicitly and support the rfc4180 standard for

Re: [sqlite] Hints for the query planner

PostgreSQL supports create index on ( ) Note that it allows an expression and not just a column name. See: http://www.postgresql.org/docs/9.1/static/sql-createindex.html Perhaps indexing the expression in question would be an alternative that would keep the performance info separate

Re: [sqlite] MONTH function

Or even: "select cast(strftime('%m') as integer)=6" On Sun, Jun 23, 2013 at 4:16 PM, Gabor Grothendieck <ggrothendi...@gmail.com> wrote: > Which can also be written as: > > "select cast(strftime('%m','now') as integer)=6" > > On Sun, Jun 23, 2013 a

Re: [sqlite] MONTH function

Which can also be written as: "select cast(strftime('%m','now') as integer)=6" On Sun, Jun 23, 2013 at 4:11 PM, Patrik Nilsson wrote: > You can write: > > "select cast(strftime('%m',datetime('now')) as integer)=6" > 1 > > > On 06/23/2013 09:45 PM, Lucas wrote: >>

Re: [sqlite] Running on windows 98

On Thu, Jan 24, 2013 at 2:21 PM, Richard Hipp wrote: > On Thu, Jan 24, 2013 at 2:01 PM, Jose F. Gimenez wrote: > >> Richard, >> >> thanks for replying. >> >> >> We have no way of testing SQLite on Win9x and so we do not intend to >>> support Win9x moving

Re: [sqlite] just a test

I am still having problems with Igor's gmail messages being marked as spam in gmail but after the upteenth time declaring them not to be spam google finally asked me if I wanted to report it to their gmail team so hopefully they will fix it soon. On Mon, Dec 3, 2012 at 11:59 PM, Clive Hayward

Re: [sqlite] SQL:2003 -- Window Functions

On Wed, Sep 19, 2012 at 12:51 PM, joe.fis...@tanguaylab.com wrote: > Too bad SQLite doesn't yet support SQL Window Functions. > > Are there any SQLite Extension Libraries that support "SQL:2003 type Window > Functions"? > I specifically need LEAD and LAG to calculate an

Re: [sqlite] Details on New Features

On Sun, May 6, 2012 at 8:00 PM, Donald Griggs wrote: > Regarding:   What precisely are the > "improvements" in handling of CSV inputs? > > > Gabor, I don't know about "precisely" -- I'll let others on the list tell > me where I'm off, but here's my take: > > > A lot of strange

Re: [sqlite] Details on New Features

On Fri, May 4, 2012 at 10:46 AM, Gabor Grothendieck <ggrothendi...@gmail.com> wrote: > On Fri, May 4, 2012 at 10:39 AM, Richard Hipp <d...@sqlite.org> wrote: >> On Fri, May 4, 2012 at 10:33 AM, Gabor Grothendieck <ggrothendi...@gmail.com >>> wrote: >> >&

Re: [sqlite] Details on New Features

On Fri, May 4, 2012 at 10:20 AM, Richard Hipp <d...@sqlite.org> wrote: > On Fri, May 4, 2012 at 10:06 AM, Rob Richardson > <rdrichard...@rad-con.com>wrote: > >> Gabor Grothendieck mentioned a new feature of SQLite in 3.7.11: >>         Queries of the form: &quo

[sqlite] Details on New Features

In this link: http://sqlite.org/releaselog/3_7_11.html it refers to these new features: Queries of the form: "SELECT max(x), y FROM table" returns the value of y on the same row that contains the maximum x value. Improvements to the handling of CSV inputs in the command-line shell Is there

Re: [sqlite] Good books to lear SQL using SQLite?

On Mon, Apr 30, 2012 at 10:18 AM, Arbol One wrote: > I am learning SQL using SQLite, can anyone tell me of a good book to learn > SQL using SQLite? > Its a web page and free software, not a book, but you might try this page to start off and then get a book after you

Re: [sqlite] Is it possible to use substrings of Windows DOS batch fiile parameters in sqlite3.exe -line db ?

On Tue, Apr 10, 2012 at 7:14 PM, Frank Chang wrote: > > Good evening, We are trying to generate automated SQLITE  SQL scripts based > on the names of SQLite tables derived by substring manipulation of Windows > DOS batch file and/or Windows environment variables. For

Re: [sqlite] how to wrie "" char in my html file?

On Sat, Mar 31, 2012 at 2:55 AM, YAN HONG YE wrote: >>sqlite3 -html C:\mydatabase\mydzh.db "select ''">mm.html > this command result is not in the mm.html file, it's this following > text: > table > > not I wanted, how to wrie char in my html file? This seems to be the

Re: [sqlite] Using SQL or C for data analysis?

On Tue, Mar 27, 2012 at 3:02 PM, Simon wrote: > select closing_price, moving_average( funky_oscillator( closing_price ) )... There is a moving average calculation in SQLite here but given the complexity you might prefer to do the analytical portion in your program:

Re: [sqlite] HOW TO EXPORT TABLE HEAD

On Mon, Mar 26, 2012 at 11:38 PM, YAN HONG YE wrote: > WHEN I export sqlite database to a html file or txt file, I couldn't know how > to include the database table head. > who can tell me? > Thank you! Use -header like this: sqlite3 -html -header my.db "select * from

Re: [sqlite] more than 2000 columns

On Mon, Nov 14, 2011 at 12:50 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 14 Nov 2011, at 5:11pm, Gabor Grothendieck wrote: > >> The requirement for a large number of columns is actually one thing >> that is often needed when using sqlite from R.  Typically the

Re: [sqlite] more than 2000 columns

On Mon, Nov 14, 2011 at 12:21 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > On 11/14/2011 12:11 PM, Gabor Grothendieck wrote: >> >> The requirement for a large number of columns is actually one thing >> that is often needed when using sqlite from R.  Typically the

Re: [sqlite] more than 2000 columns

On Mon, Nov 14, 2011 at 5:32 AM, Simon Slavin wrote: > > On 14 Nov 2011, at 7:38am, vinayh4 wrote: > >> I need to create table with more than 2000 columns, How to reset >> SQLITE_MAX_COLUMN value which >> is  2000 . Plz help me on this issue. > > You almost never need to

Re: [sqlite] SQLITE_MAX_VARIABLE_NUMBER

On Tue, Nov 8, 2011 at 7:11 PM, Richard Hipp <d...@sqlite.org> wrote: > On Tue, Nov 8, 2011 at 7:08 PM, Gabor Grothendieck > <ggrothendi...@gmail.com>wrote: > >> On Tue, Nov 8, 2011 at 6:46 PM, Richard Hipp <d...@sqlite.org> wrote: >> > On Tue,

Re: [sqlite] SQLITE_MAX_VARIABLE_NUMBER

On Tue, Nov 8, 2011 at 6:46 PM, Richard Hipp <d...@sqlite.org> wrote: > On Tue, Nov 8, 2011 at 5:50 PM, Gabor Grothendieck > <ggrothendi...@gmail.com>wrote: > >> In R, the RSQLite driver for SQLite currently has >> SQLITE_MAX_VARIABLE_NUMBER set to 999.  This i

Re: [sqlite] SQLITE_MAX_VARIABLE_NUMBER

On Tue, Nov 8, 2011 at 5:55 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 8 Nov 2011, at 10:50pm, Gabor Grothendieck wrote: > >> In R, the RSQLite driver for SQLite currently has >> SQLITE_MAX_VARIABLE_NUMBER set to 999.  This is used by many people >

[sqlite] SQLITE_MAX_VARIABLE_NUMBER

In R, the RSQLite driver for SQLite currently has SQLITE_MAX_VARIABLE_NUMBER set to 999. This is used by many people for many different projects and on different platforms and it seems that a number of these projects want a larger number. Users don't compile this themselves so they are stuck

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On Tue, Sep 27, 2011 at 2:14 PM, David Garfield wrote: > Any entry in a pipe could be buffering.  In a quick test here, awk is > buffering.  To find the buffering, try using the pieces up to a given > stage with " | cat " added at the end.  If this buffers, you've

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

On Mon, Jun 6, 2011 at 11:54 AM, Richard Hipp wrote: > On Mon, Jun 6, 2011 at 11:44 AM, Sidney Cadot wrote: > >> >> Would it be useful to open a ticket on this issue, or will it never be >> changed e.g. for fear of breaking backward compatibility? >> > > There

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

On Wed, Dec 8, 2010 at 2:44 PM, Richard Hipp wrote: > On Wed, Dec 8, 2010 at 2:35 PM, Petite Abeille > wrote: > >> Hello, >> >> The pragma foreign_key_list appears to be deprecated in 3.7.4: >> >>

Re: [sqlite] Slow SELECT Statements in Large Database file

On Fri, Oct 29, 2010 at 12:07 PM, Jonathan Haws wrote: > All, > > I am having some problems with a new database that I am trying to setup. > > This database is a large file (about 8.7 GB without indexing).  The problem > I am having is that SELECT statements are

Re: [sqlite] GUI for data entry

On Fri, Oct 15, 2010 at 12:54 PM, Graham Smith wrote: > Tom, > > Thanks for this. > > My main reason for asking is because I am trying to encourage my > students and indeed clients to think "database" rather than > "spreadsheet". Most of the time these aren't big or complex

Re: [sqlite] Involving in sqlite development

On Thu, Oct 7, 2010 at 5:26 PM, sjtirtha wrote: > Hi, > > I'm interested involving in sqlite development. > How can I start it? > If I can add to this question is there a posted wishlist, todo list or roadmap? Something like this: http://www.h2database.com/html/roadmap.html

Re: [sqlite] Which data type is better for date?

On Sat, Sep 4, 2010 at 7:31 AM, Mike Zang wrote: > I try to convert data to SQLite3 for iPad, please give me some detail > suggestion. > > I think that I can save date value as below to SQLite3, I want to know > which is better, or anything else if you have good idea. > > 1.

Re: [sqlite] 5000 tables with 3000 records vs 1 table with 15000000 records

On Sun, Aug 22, 2010 at 4:51 AM, Mike Zang wrote: > I have 5000 files and I want to converrt them to SQLite3 on iPad, now I > have a performance question, I am not sure which way is better for select > and insert data in SQLite3. > > I have  two ideas for converting. > > 1.

Re: [sqlite] Doing fine with SQLite

You don't need to dump the data to a csv file and then read it into R and there is no need to use the sqlite3 console at all as R's RSQLite package can directly read and write SQLite databases. Also see the sqldf package. On Tue, May 4, 2010 at 1:02 PM, Matt Young wrote:

Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?

SQLite and R are already integrated through the RSQLite/DBI packages and even higher level facilities (which I have posted about on this thread) also exist. I think that such an integration would logically be done by R people rather than sqlite people. In the discussion on this thread

Re: [sqlite] scripting language interpreter

On Sat, May 1, 2010 at 12:15 PM, Richard Hipp wrote: > On Sat, May 1, 2010 at 9:25 AM, Tim Romano wrote: > >> I am aware that SQLite supports >> loadable extensions, but would the SQLite architecture also permit the >> integration of an interpreted

Re: [sqlite] STandard Deviation

Not in sqlite itself but stddev_samp and stddev_pop are available in the spatialite loadable extension. Be careful since they interchanged sample and population in one version of the extension. On Sun, Apr 18, 2010 at 9:37 PM, Peter Haworth wrote: > Does Sqlite have a

Re: [sqlite] how to submit a file with sql to sqlite"

On Sun, Apr 18, 2010 at 12:02 PM, Wensui Liu wrote: > dear listers, > i am wondering if there is a way to submit a file with many sql > statements, say several hundred lines,  to sqlite. > > thanks for your insight. C:\tmp2>type a.sql create table tab (a,b); insert into tab

Re: [sqlite] Northwind example database

It would be nice if SQLite had a strptime-like function for things like this and not just strftime. On Mon, Mar 29, 2010 at 10:13 AM, Griggs, Donald wrote: > > > On 27 Mar 2010, at 10:46am, GeoffW wrote: > >> Just for educational purposes I have been experimenting a

Re: [sqlite] Get a specific sequence of rows...

On Fri, Mar 26, 2010 at 5:00 AM, Fredrik Karlsson wrote: > Hi, > > I have a list of id:s stored in a field. I would now like to get some > information from a table by these id:s, but exactly in this order. So, > if I have a table > > 1 One > 2 Two > 3 Three > > and the