Re: [sqlite] Limit on number of columns in SQLite table

On Wed, Nov 6, 2019 at 7:22 PM Jens Alfke wrote: > > > > On Nov 6, 2019, at 9:25 AM, Digital Dog wrote: > > > > If there are millions or billions of rows > > in the data set I consider it big data and the only reasonable format for > > storing it is a column store format. > > There are many

Re: [sqlite] Limit on number of columns in SQLite table

> On Nov 6, 2019, at 9:25 AM, Digital Dog wrote: > > If there are millions or billions of rows > in the data set I consider it big data and the only reasonable format for > storing it is a column store format. There are many types of stores for "big data". My employer, Couchbase, has

Re: [sqlite] Limit on number of columns in SQLite table

Hi! I'm late to the party, but want to contribute. I did not read all messages in the thread, but in those I've read did not contain a question about number of ROWS. We know how many columns you desire, but how many rows are there? No matter how I like SQLite, I would not store this kind of data

Re: [sqlite] Limit on number of columns in SQLite table

""Hi! On Thu, Oct 17, 2019 at 5:38 PM Jens Alfke wrote: > Why should SQLite make changes, which would introduce performance problems if > used, just to save your particular application the trouble of concatenating > some vectors into single columns, when it uses SQLite for an edge use-case >

Re: [sqlite] Limit on number of columns in SQLite table

—Jens > On Oct 17, 2019, at 1:56 AM, Mitar wrote: > > So why not increase the limit to 2 billion Why should SQLite make changes, which would introduce performance problems if used, just to save your particular application the trouble of concatenating some vectors into single columns, when

Re: [sqlite] Limit on number of columns in SQLite table

Hi! Oh, or we could just split CSV into separate lines, and then just store one line per SQLite row, into one column. Then we do not have to use JSON or something. That would work for CSV files. For other types of inputs we might be able to find some other similar approach. So generally the

Re: [sqlite] Limit on number of columns in SQLite table

So if character-separated values (CSV-ish) were originally your preferred import format, would using that format for the blob's work for you? E.g., Suppose you need to index the first two fields only, and so can use a blob column for the bulk of the record. If the records were supplied as:

Re: [sqlite] Limit on number of columns in SQLite table

Hi! On Thu, Oct 17, 2019 at 3:04 PM Eric Grange wrote: > my suggestion would be to store them as JSON in a blob, and use the JSON > functions of SQLite to extract the data JSON has some crazy limitations like by standard it does not support full floating point spec, so NaN and infinity cannot

Re: [sqlite] Limit on number of columns in SQLite table

> I wrote earlier that for us use case where we are reading whole rows is the most common one. > [...] > we are looking for ways to store this in a stable format which will be supported for next 50 years, without modifying to original data too much. If you do not need access to individual columns

Re: [sqlite] Limit on number of columns in SQLite table

Hi! This is getting a bit off topic. On Thu, Oct 17, 2019 at 12:07 PM Simon Slavin wrote: > 1) Almost no piece of software can handle a grid 2 billion cells wide. Excel > maxes out at 16,384 columns. Matlab can store and retrieve a cell of data > directly from a file, but it has a max array

Re: [sqlite] Limit on number of columns in SQLite table

On 17 Oct 2019, at 9:56am, Mitar wrote: > I can understand how supporting a large number of columns might be > inappropriate when you want to run complicated SQL queries on data, > but to just store data and then extract all rows to do some data > processing, Or as the most complicated query it

Re: [sqlite] Limit on number of columns in SQLite table

Hi! I can see how this is a reasonable limit when SQLite is used for querying power it provides. In our case we are really focusing on it as a standard long-term storage format. So in the "Appropriate Uses For SQLite" document [1] you have a section called "File archive and/or data container" and

Re: [sqlite] Limit on number of columns in SQLite table

> On Oct 16, 2019, at 6:08 AM, Mitar wrote: > > Quite > some of datasets we are dealing with have 100k or so columns. There was a thread about this a few months ago. You Should not store every number of a huge vector in a separate column. You don’t need to individually query on every

Re: [sqlite] Limit on number of columns in SQLite table

SQLite could, in theory, be enhanced (with just a few minor tweaks) to support up to 2 billion columns. But having a relation with a large number of columns seems like a very bad idea stylistically. That's not how relational databases are intended to be used. Normally when a table acquires more

Re: [sqlite] Limit on number of columns in SQLite table

Hi! On Wed, Oct 16, 2019 at 3:29 PM Richard Hipp wrote: > Are you trying to store a big matrix with approx 100k columns? A > better way to do that in a relational database (*any* relational > database, not just SQLite) is to store one entry per matrix elements: Sure, this is useful for sparse

Re: [sqlite] Limit on number of columns in SQLite table

On 10/16/19, Mitar wrote: > Hi! > > We are considering using SQLite as a ML dataset archival format for > datasets in OpenML (https://www.openml.org/). When investigating it, > we noticed that it has a very low limit on number of columns. Quite > some of datasets we are dealing with have 100k or

[sqlite] Limit on number of columns in SQLite table

Hi! We are considering using SQLite as a ML dataset archival format for datasets in OpenML (https://www.openml.org/). When investigating it, we noticed that it has a very low limit on number of columns. Quite some of datasets we are dealing with have 100k or so columns. Are there any fundamental

Re: [sqlite] LIMIT

> Don't want to spoil any news, it is viewable by the public anyway, but there > are clearly experimental, interesting (and significant) work ongoing by the > SQLite developers around SQL windowing functions. A quick look to the > exp-window-functions branch clearly shows that. They are part

Re: [sqlite] LIMIT

Just wanted to support the idea of adding windowing functions to SQLite as it is the biggest hurdle for me to get others to use it. If size is an issue, I would love to have a build option to enable it. My $0.02 On Tue, Jun 26, 2018 at 8:27 AM Gert Van Assche wrote: > Thanks Olivier, very

Re: [sqlite] LIMIT

Thanks Olivier, very good to know. gert Op di 26 jun. 2018 om 09:06 schreef Olivier Mascia : > > Le 26 juin 2018 à 07:56, Gert Van Assche a écrit : > > > > If I would like to use these ranking techniques in SQLite, can I do this > > with an extension? Has nobody created a Windowing.dll yet? >

Re: [sqlite] LIMIT

> Le 26 juin 2018 à 07:56, Gert Van Assche a écrit : > > If I would like to use these ranking techniques in SQLite, can I do this > with an extension? Has nobody created a Windowing.dll yet? > Just asking. My short term need has been solved, but now that I know this, > I'm sure I'd like to use

Re: [sqlite] LIMIT

Very interesting, Ryan. Thanks for sharing. If I would like to use these ranking techniques in SQLite, can I do this with an extension? Has nobody created a Windowing.dll yet? Just asking. My short term need has been solved, but now that I know this, I'm sure I'd like to use this technique

Re: [sqlite] LIMIT

On 2018/06/24 9:30 PM, Gert Van Assche wrote: whaw... I could never come up with solutions like this. Will this work with SQLite? Where can I read more about this? I've only shown that query since Dan asked about it - it's not an SQLite query. To answer/comment on your questions: 1. Sure

Re: [sqlite] LIMIT

whaw... I could never come up with solutions like this. Will this work with SQLite? Where can I read more about this? Op zo 24 jun. 2018 om 20:29 schreef R Smith : > > > On 2018/06/22 11:05 PM, Dan Kennedy wrote: > > On 06/23/2018 03:52 AM, R Smith wrote: > >> > >> On 2018/06/22 10:04 PM, Gert

Re: [sqlite] LIMIT

On 2018/06/22 11:05 PM, Dan Kennedy wrote: On 06/23/2018 03:52 AM, R Smith wrote: On 2018/06/22 10:04 PM, Gert Van Assche wrote:   All, I'm sure it must be possible, I just don't find how. I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2 are not unique. I would

Re: [sqlite] LIMIT

I checked with a fake dataset: the GROUP BY solution is quicker with no index on F2 (the query planner can understand that query well enough to create an automatic covering index). However, with an index on F2, the rowid solution is considerably faster for a dataset with 2600 random distinct F2

Re: [sqlite] LIMIT

Ryan, my dataset isn't that big. 11K records. Your solution is noticeable faster 996 ms vs 13126 ms. Interesting! gert Op za 23 jun. 2018 om 18:09 schreef R Smith : > > On 2018/06/23 2:47 PM, Gert Van Assche wrote: > > Barry, that's even easier indeed. And it works perfect! > > Thanks for

Re: [sqlite] LIMIT

On 23 Jun 2018, at 5:08pm, R Smith wrote: > May I ask, if you do have a rather large dataset, and perhaps don't mind the > time, would you care to compare the two methods and let us know if there is > any discernible difference in speed? Answers will be different depending on type of main

Re: [sqlite] LIMIT

On 2018/06/23 2:47 PM, Gert Van Assche wrote: Barry, that's even easier indeed. And it works perfect! Thanks for sharing this. I very much like the method which Barry suggested for the simplicity, but have avoided it since I thought it would be significantly slower on a large data set,

Re: [sqlite] LIMIT

Barry, that's even easier indeed. And it works perfect! Thanks for sharing this. gert Op za 23 jun. 2018 om 14:32 schreef Barry Smith : > Ryan's way works well. Here is a second method which expresses it in a > different way: > > SELECT * FROM T t1 where rowid IN (SELECT rowid FROM T t2 WHERE

Re: [sqlite] LIMIT

Ryan's way works well. Here is a second method which expresses it in a different way: SELECT * FROM T t1 where rowid IN (SELECT rowid FROM T t2 WHERE t1.F2 = t2.F2 ORDER BY rowid LIMIT 10) If you have WITHOUT ROWID tables you'd have to replace rowid with your primary key. (The query may

Re: [sqlite] LIMIT

Hi Ryan, thanks for this. This is working if the F1 field is a numeric value. With text (especially Asian & Arabic characters) this does not seem to work. So I created an MD5 hash from the text fields and it works great! Thank you so much. gert Op vr 22 jun. 2018 om 22:52 schreef R Smith : > >

Re: [sqlite] LIMIT

> What would the window-function query be? Note: For anyone stumbling upon this thread, below code is not supported in SQLite natively. SELECT F1, F2 FROM ( SELECT F2, F1, Rank() OVER (PARTITION BY F2 ORDER BY F1 ) AS Rank FROM T )

Re: [sqlite] LIMIT

On 06/23/2018 03:52 AM, R Smith wrote: On 2018/06/22 10:04 PM, Gert Van Assche wrote: All, I'm sure it must be possible, I just don't find how. I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2 are not unique. I would like to get only 10 F1 fields for each unique F2.

Re: [sqlite] LIMIT

On 2018/06/22 10:04 PM, Gert Van Assche wrote: All, I'm sure it must be possible, I just don't find how. I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2 are not unique. I would like to get only 10 F1 fields for each unique F2. This is not normally done, and

[sqlite] LIMIT

All, I'm sure it must be possible, I just don't find how. I have a table T with 2 fields (F1 and F2). The F1 are unique and the F2 are not unique. I would like to get only 10 F1 fields for each unique F2. LIMIT 10 is the easy part... But how do I express the query so it F1 is LIMITed to 10 for

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Hi Richard, Today, i verified with android NDK 17 and It is working fine without disabling compiler optimization. Thanks, Sathish On Mon, May 21, 2018 at 9:41 PM, Sathish Kumar wrote: > Thanks, I will do that. > > Thanks, > Sathish > > > On Mon 21 May, 2018, 9:34

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On Mon, May 21, 2018 at 6:04 PM Richard Hipp wrote: > > LOCAL_CFLAGS += -O0 > > > > LOCAL_CPPFLAGS += -O0 > Looks like you have found a bug in clang. I recommend updating to the > latest version of the compiler that you can get your hands on, and see > if that doesn't fix the

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Thanks, I will do that. Thanks, Sathish On Mon 21 May, 2018, 9:34 PM Richard Hipp, wrote: > On 5/21/18, Sathish Kumar wrote: > > Hi Richard, > > Yes, it works if i disable the compiler optimization. Below is what > i > > used in my Android.mk

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On 5/21/18, Sathish Kumar wrote: > Hi Richard, > Yes, it works if i disable the compiler optimization. Below is what i > used in my Android.mk file. Will it create any side effects if we disable > compiler optimization ? > > LOCAL_CFLAGS += -O0 > > LOCAL_CPPFLAGS +=

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Hi Richard, Yes, it works if i disable the compiler optimization. Below is what i used in my Android.mk file. Will it create any side effects if we disable compiler optimization ? LOCAL_CFLAGS += -O0 LOCAL_CPPFLAGS += -O0 Thanks, Sathish On Fri, May 18, 2018 at 7:28 PM, Sathish Kumar

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Ok, I will try. On Fri 18 May, 2018, 7:15 PM Richard Hipp, wrote: > On 5/18/18, Sathish Kumar wrote: > > Hi Richard, > > Further update on this issue. > > > > I am observing this issue only with the library build using ndk-build > > via terminal

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On 5/18/18, Sathish Kumar wrote: > Hi Richard, > Further update on this issue. > > I am observing this issue only with the library build using ndk-build > via terminal and the library built via android studio gradle works fine. Is > there any difference in

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Hi Richard, Further update on this issue. I am observing this issue only with the library build using ndk-build via terminal and the library built via android studio gradle works fine. Is there any difference in compiling sqlite using gradle and via ndk-build via terminal ? Thanks,

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Hi Richard, Is there any issue with this compile option ? Thanks, Sathish On Wed 16 May, 2018, 5:30 PM Sathish Kumar, wrote: > sqlite> SELECT sqlite_source_id(); > > 2017-06-08 14:26:16 > 0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b > > >

Re: [sqlite] LIMIT with OFFSET is not working in android platform

sqlite> SELECT sqlite_source_id(); 2017-06-08 14:26:16 0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b sqlite> PRAGMA compile_options; COMPILER=clang-5.0.300080 DEFAULT_SYNCHRONOUS=2 DEFAULT_WAL_SYNCHRONOUS=2 ENABLE_FTS3 ENABLE_FTS5 ENABLE_JSON1 ENABLE_RTREE

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On 05/16/2018 06:20 PM, Sathish Kumar wrote: Hi, Sorry, Please find the full sequence below, it is taken from S9 device running with Android 8.0. This is a bit strange. The VM code looks correct. Try running a [make clean] to ensure you're getting a clean build. If that doesn't work,

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On 5/16/18, Sathish Kumar wrote: > Samsung S9 device with Android 8.0 What output do you see from the following commands: SELECT sqlite_source_id(); PRAGMA compile_options; -- D. Richard Hipp d...@sqlite.org ___

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Samsung S9 device with Android 8.0 On Wed, May 16, 2018 at 4:50 PM, Sathish Kumar wrote: > Hi, > Sorry, Please find the full sequence below, it is taken from S9 device > running with Android 8.0. > > sqlite> create table test (id integer primary key autoincrement, name

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Hi, Sorry, Please find the full sequence below, it is taken from S9 device running with Android 8.0. sqlite> create table test (id integer primary key autoincrement, name text); sqlite> insert into test (name) values('a'); sqlite> insert into test (name) values('a'); sqlite> insert into test

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On 5/16/18, Sathish Kumar wrote: > I just skipped those statements in mail, but i inserted 7 rows. Please send *exactly* the sequence of commands that you think are producing an incorrect answer. Please leave nothing to chance, or to interpretation. -- D. Richard Hipp

Re: [sqlite] LIMIT with OFFSET is not working in android platform

I just skipped those statements in mail, but i inserted 7 rows. On Wed 16 May, 2018, 4:06 PM Richard Hipp, wrote: > On 5/16/18, Sathish Kumar wrote: > > Hi, > > Please find the below example. > > > > sqlite> create table test (id integer primary key

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Hi, Please find the below example. sqlite> create table test (id integer primary key autoincrement, name text); sqlite> insert into test (name) values('a'); sqlite> insert into test (name) values('a'); sqlite> select count(*) from test; 7 sqlite> select * from test limit 10 offset 2; 2|a 3|a

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On 5/16/18, Sathish Kumar wrote: > Hi, > Please find the below example. > > sqlite> create table test (id integer primary key autoincrement, name > text); > sqlite> insert into test (name) values('a'); > sqlite> insert into test (name) values('a'); > sqlite> select

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On 5/16/18, Sathish Kumar wrote: > Hi All, > OFFSET clause is not working as expected in android platform. > > [ ISSUE ] : eg. select * from test limit 2 offset 5; > > [ Expected Result ] : This query should return two rows starting from index > 6. > > [ Actual Result

[sqlite] LIMIT with OFFSET is not working in android platform

Hi All, OFFSET clause is not working as expected in android platform. [ ISSUE ] : eg. select * from test limit 2 offset 5; [ Expected Result ] : This query should return two rows starting from index 6. [ Actual Result ] : It always returns rows starting from index 2, even though it had

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

Hello, On 2018-02-16 13:00, Dominique Devienne wrote: While you're technically right, I think of SQL as a declarative language, and as such I'd say giving this information to SQLite is a best practice IMHO. Unlikely in this case, but perhaps one day SQLite might be able to optimize "something"

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

I suppose ‘select * from (original select with limit clause) limit :lim’ gets round it. From: Dominique Devienne<mailto:ddevie...@gmail.com> Sent: 16 February 2018 12:21 To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] LIMIT versus sqlite

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

On Fri, Feb 16, 2018 at 1:11 PM, x wrote: > Thanks for the replies. For my purpose it was about avoiding the > possibility of having to apply a limit to a query that might already have a > limit clause. Good point. I tried, and indeed that's an issue. I really really

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

ite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] LIMIT versus sqlite3_step LIMIT times On Fri, Feb 16, 2018 at 12:33 PM, Cezary H. Noweta <c...@poczta.onet.pl> wrote: > On 2018-02-16 11:18, x wrote: > >> If a query is sorted on an index is there any advantage to including &

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

On Fri, Feb 16, 2018 at 12:33 PM, Cezary H. Noweta wrote: > On 2018-02-16 11:18, x wrote: > >> If a query is sorted on an index is there any advantage to including >> LIMIT in the stmt as opposed to omitting it and stepping through the result >> set LIMIT times? >> > > No --

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

Hello, On 2018-02-16 11:18, x wrote: If a query is sorted on an index is there any advantage to including LIMIT in the stmt as opposed to omitting it and stepping through the result set LIMIT times? No -- LIMIT appends an additional opcode to check the number of rows and introduces an

[sqlite] LIMIT versus sqlite3_step LIMIT times

If a query is sorted on an index is there any advantage to including LIMIT in the stmt as opposed to omitting it and stepping through the result set LIMIT times? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] LIMIT doesn't return expected rows

Am Mittwoch, 12. Oktober 2016, 12:11:11 CEST schrieb Richard Hipp: > On 10/11/16, Keith Medcalf wrote: > > #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1 > > > > makes it work properly. neither NDEBUG nor SQLITE_DEBUG explicitly > > defined. > > > > Over to Richard ... > >

Re: [sqlite] LIMIT doesn't return expected rows

Works for me building the sqlite3 command line tool from the prerelease snapshot, on both Mac and Windows. SQLite version 3.15.0 2016-10-12 15:15:30 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .read

Re: [sqlite] LIMIT doesn't return expected rows

On 10/11/16, Keith Medcalf wrote: > > #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1 > > makes it work properly. neither NDEBUG nor SQLITE_DEBUG explicitly defined. > > Over to Richard ... Should now be fixed on trunk and in the latest Prerelease Snapshot at

Re: [sqlite] LIMIT doesn't return expected rows

Tuesday, 11 October, 2016 19:22 > To: SQLite mailing list > Subject: Re: [sqlite] LIMIT doesn't return expected rows > > > By default, I do not see the query being flattened. Flattening it > manually produces the same right results independant of something config.h > does.

Re: [sqlite] LIMIT doesn't return expected rows

EVERSE_UNORDERED_SELECTS 1 > // #define SQLITE_USE_FCNTL_TRACE 1// Enable > extra vfslog fcntrl trace > // #define SQLITE_YYTRACKMAXSTACKDEPTH 1 > > #if defined(_WIN32) && defined(__GNUC__) > #define UNICODE_STRING_MAX_BYTES ((WORD) 65534) > #define UNICODE_STRI

Re: [sqlite] LIMIT doesn't return expected rows

sday, 11 October, 2016 18:41 > To: SQLite mailing list > Subject: Re: [sqlite] LIMIT doesn't return expected rows > > Following up: same for the Mac distribution of 3.14.2 command line tool. > Using the pre-release snapshot of 3.15.0 from the main download page to > build the sq

Re: [sqlite] LIMIT doesn't return expected rows

and it works correctly. >> >> Can you check the result of "select sqlite_source_id();", which should be: >> >> 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6 >> >> https://www.sqlite.org/src/info/29dbef4b8585f753 >> >>> --

Re: [sqlite] LIMIT doesn't return expected rows

k the result of "select sqlite_source_id();", which should be: > > 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6 > > https://www.sqlite.org/src/info/29dbef4b8585f753 > >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailin

Re: [sqlite] LIMIT doesn't return expected rows

53861a36d6dd102ca634197bd6 https://www.sqlite.org/src/info/29dbef4b8585f753 > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Tobias Ellinghaus > Sent: Tuesday, 11 October, 2016 11:41 > To: sqlite-users@mailinglists.sqlite.

Re: [sqlite] LIMIT doesn't return expected rows

On 10/11/2016 07:57 PM, Simon Slavin wrote: On 11 Oct 2016, at 1:44pm, Eric Minbiole wrote: Your problem is that although SQL accepts the clauses written in the order you wrote them in, the LIMIT clause is processed before the ORDER BY clause. Is that really true? I had

Re: [sqlite] LIMIT doesn't return expected rows

Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf: > This was fixed September 7. The fix appears in 3.14.2 and also on the > current 3.15.0. Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the last query? I am asking as that's the version I am using

Re: [sqlite] LIMIT doesn't return expected rows

On 11 Oct 2016, at 1:44pm, Eric Minbiole wrote: >> Your problem is that although SQL accepts the clauses written in the order >> you wrote them in, the LIMIT clause is processed before the ORDER BY clause. > > Is that really true? I had always thought that the ORDER BY was

Re: [sqlite] LIMIT doesn't return expected rows

ROM m) ORDER BY flags DESC limit 3; 6 5 7 > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Tobias Ellinghaus > Sent: Tuesday, 11 October, 2016 04:53 > To: sqlite-users@mailinglists.sqlite.org > Subject

Re: [sqlite] LIMIT doesn't return expected rows

> Your problem is that although SQL accepts the clauses written in the order > you wrote them in, the LIMIT clause is processed before the ORDER BY clause. > > Is that really true? I had always thought that the ORDER BY was processed first, though I admit I don't see anything authoritative either

Re: [sqlite] LIMIT doesn't return expected rows

On 11 Oct 2016, at 11:52am, Tobias Ellinghaus wrote: > Now I only want the first three values, 6, 5 and 7. However: > > sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC > LIMIT 0, 3; > 1 > 2 > 3 Your problem is that although SQL accepts the clauses

[sqlite] LIMIT doesn't return expected rows

Hello, first let me mention that I am new to this list so apologies if my question came up before. I couldn't find anything though, and in #sqlite on Freenode I was pointed here, so here I am. I am working on some code that creates a (potentially big) SQL query on the fly. My problem is, that

Re: [sqlite] Limit to 5 records per Group / Top N results per group

On 6/1/2016 5:36 PM, Russell, Rory wrote: In SQLIte, I have 2 tables, one is for a list of Projects (about 50 projects) and the other is a list of key dates for each project. Each project has about 20 key dates. How can run a query that will only return the top/first 5 dates for each project in

[sqlite] Limit to 5 records per Group / Top N results per group

All, Please help.I have been trying but can't find an answer. In SQLIte, I have 2 tables, one is for a list of Projects (about 50 projects) and the other is a list of key dates for each project. Each project has about 20 key dates. How can run a query that will only return the top/first 5

[sqlite] Limit size of write-ahead log file

On 5/19/15, Kees Nuyt wrote: > Did you consider > PRAGMA wal_autocheckpoint=N; and > PRAGMA database.wal_checkpoint(); ? > > http://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint Thank you for your reply. Yes, but that will not always limit the size of the wal file. I believe

[sqlite] Limit size of write-ahead log file

On Mon, 18 May 2015 19:00:28 +0200, Zsb?n Ambrus wrote: >The PRAGMA max_page_count statement lets me set a limit on the size >the database is allowed to grow. This is useful to protect myself >against accidental errors in my program, where I fill the file system >with a huge database file,

[sqlite] Limit size of write-ahead log file

The PRAGMA max_page_count statement lets me set a limit on the size the database is allowed to grow. This is useful to protect myself against accidental errors in my program, where I fill the file system with a huge database file, which could disrupt other processes that are trying to write the

[sqlite] LIMIT expression in subquery cannot reference outer query tables

When using LIMIT in a subquery it seems the expression cannot access the outer query's tables. Other complex expressions are supported in LIMIT, so I was curious if this is a defined limitation or something else? Example: sqlite> create table test (f int); sqlite> insert into test values (1);

Re: [sqlite] Limit of attached databases

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/06/13 08:14, Eleytherios Stamatogiannakis wrote: > We have tried with both views and VTs but SQLite does not create > automatic indexes on them at all. So right now, to be able to have > automatic indexes from SQLite's side we materialize all

Re: [sqlite] Limit of attached databases

On 04/06/13 17:37, Simon Slavin wrote: On 4 Jun 2013, at 3:09pm, Eleytherios Stamatogiannakis wrote: Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read only* attached DBs? See section 11 of It's a 64-bit value, and

Re: [sqlite] Limit of attached databases

est...@gmail.com; 'General Discussion of SQLite Database' Subject: Re: [sqlite] Limit of attached databases Gcc does have a __int128_t and __uint128_t available if you're on 64-bit and have a current enough gcc (I'm using 4.4.4 and this works on Linux and Windows) Looks like a fairly easy cha

Re: [sqlite] Limit of attached databases

-boun...@sqlite.org] On Behalf Of Eleytherios Stamatogiannakis Sent: Tuesday, June 04, 2013 9:09 AM To: sqlite-users@sqlite.org Subject: [sqlite] Limit of attached databases Hi, During our work on a distributed processing system (which uses SQLite shards), we have hit the SQLITE_MAX_ATTACHED limit of att

Re: [sqlite] Limit of attached databases

On 4 Jun 2013, at 3:09pm, Eleytherios Stamatogiannakis wrote: > Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read only* > attached DBs? See section 11 of It's a 64-bit value, and two bits are already taken up. You

[sqlite] Limit of attached databases

Hi, During our work on a distributed processing system (which uses SQLite shards), we have hit the SQLITE_MAX_ATTACHED limit of attached DBs. The way we use SQLite for distributed processing [*], is the following: - Each table is sharded into multiple SQLite DBs on different nodes of the

Re: [sqlite] Limit on the Compound Select Statements

Would it make more sense to put the values into a text file and import the text file? It separates the data from the application, and simplifies making future changes to the list. On Thu, Feb 23, 2012 at 1:52 PM, Abhinav Upadhyay < er.abhinav.upadh...@gmail.com> wrote: > On Thu, Feb 23, 2012 at

Re: [sqlite] Limit on the Compound Select Statements

On Thu, Feb 23, 2012 at 6:50 PM, Simon Slavin wrote: > > On 23 Feb 2012, at 1:16pm, Abhinav Upadhyay > wrote: > >> I do not remember the >> exact error message but it close to this. As per the documentation on >> the compound select

Re: [sqlite] Limit on the Compound Select Statements

On Thu, Feb 23, 2012 at 8:25 AM, Petite Abeille wrote: > > On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote: > > > . I was wondering if I could insert them using a > > single INSERT query > > Ah, also, there is not much benefit in using a compound insert. > > You

Re: [sqlite] Limit on the Compound Select Statements

On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote: > . I was wondering if I could insert them using a > single INSERT query Ah, also, there is not much benefit in using a compound insert. You could as well simply insert all your values in one transaction and be done. On the other hand, the

Re: [sqlite] Limit on the Compound Select Statements

On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote: > I would like to know the exact limit > on this, so that I could my code to work within this limit :) See Maximum Number Of Terms In A Compound SELECT Statement: http://www.sqlite.org/limits.html

Re: [sqlite] Limit on the Compound Select Statements

On 23 Feb 2012, at 1:16pm, Abhinav Upadhyay wrote: > I do not remember the > exact error message but it close to this. As per the documentation on > the compound select statements > (http://www.sqlite.org/lang_select.html) on Sqlite website, there is > no mention

[sqlite] Limit on the Compound Select Statements

Hi, I have a single column table, in which I wish to store around several thousands of rows. I was wondering if I could insert them using a single INSERT query and came across this Stackoverflow answer: http://stackoverflow.com/a/1734067/348637 . According to that answer it is possible to insert

Re: [sqlite] Limit COUNT

> boun...@sqlite.org] On Behalf Of Bart Smissaert > > That seems to be the answer and after some quick testing it looks it makes it > more efficient as well! > > On Tue, Oct 18, 2011 at 1:36 PM, Kit wrote: > > 2011/10/16 Fabian : > >> How can you

Re: [sqlite] Limit COUNT

That seems to be the answer and after some quick testing it looks it makes it more efficient as well! RBS On Tue, Oct 18, 2011 at 1:36 PM, Kit wrote: > 2011/10/16 Fabian : >> How can you limit a count-query? I tried: >> SELECT COUNT(*) FROM table

  1   2   >