Re: [sqlite] Group by Literals

Just realized, the section I've quoted from ORDER BY, not GROUP BY, but the effect pertains the both. On 2017/05/24 2:38 PM, R Smith wrote: This is quite clear in the documentation I think, and might even be made clear in the SQL standard (but I did not check). An integer literal (and only

Re: [sqlite] Group by Literals

Denis Burke wrote: > The SQLite documentation (http://www.sqlite.org/lang_select.html) says the > GROUP BY clause accepts [expr]. And [expr] can be composed of a literal. > What I cannot find is what SQLite does (or should do) with a literal in the > GROUP BY clause. SQL-92 doesn't allow it: |

Re: [sqlite] Group by Literals

The number 1 references the first column of the result set, 2 the second, and 3 is an error because there are only 2 columns. If the expression is a constant, then there is only 1 group. You are missing any meaningful information because you do not have an aggregate expression in your select

Re: [sqlite] SQLite3.Step fast forward / skipping rows

You need to write your application like a telephone directory. To get to the page with the "Smithson" entry on it, you do not read all the entries starting from the begining until you get there -- you turn directly to the page you want by doing a search. Surely you have a unique key for the

Re: [sqlite] SQLite3.Step fast forward / skipping rows

Yes, but this would still be slow, because lastValue is lets say page 50 in the telephone directory, but I need to go to page 800. So this query would still return all pages from 50 to 800, which I dont need. Am 24.05.2017 um 10:45 schrieb Andy Ling: Then when you detect a jump you'll need

Re: [sqlite] SQLite3.Step fast forward / skipping rows

The trick is to have a way to identify the first/current row and use that in the WHERE clause. e.g. SELECT ... FROM customers WHERE customer_id >= last_displayed LIMIT window_size If your select statement is a complex join without any usable key, you will have to resort to storing the results

Re: [sqlite] Group by Literals

This is quite clear in the documentation I think, and might even be made clear in the SQL standard (but I did not check). An integer literal (and only an integer literal) denotes the column number to order or group by. This is true for all Databases I know of, but that list is obviously not

Re: [sqlite] Group by Literals

Apologies for the multiple posts, but having now read the documentation thoroughly, I think the OP has a point and the GROUP BY documentation can benefit from local inclusion of the integer constant explanation that is given later for ORDER-BY (as quoted below) - or perhaps simply

Re: [sqlite] SQLite3.Step fast forward / skipping rows

You are asking the DB to give you all the 8000...+ results, sort them and then you opt to only look at some of them, there is no way this can ever be fast in any system, you need to rethink how you ask for information. First things first, you should never be using the sqlite (or any other

Re: [sqlite] SQLite3.Step fast forward / skipping rows

Almost, but when you compare to a telephone directory, then the use case of fast scrolling down in a listbox would be going directly to page 800 and not going to "Smithson". And yes, there is a unique key, but this doesn't help, because the list can also be sorted to various columns. Am

Re: [sqlite] SQLite3.Step fast forward / skipping rows

I might be wrong, but to me this sounds like an application coding problem to do with your listview. You will need to code in such a way that the listview doesn't get updated when it doesn' t have to. Does the data come directly from the DB or is there an intermediate eg an array? RBS On Wed,

[sqlite] Group by Literals

The SQLite documentation (http://www.sqlite.org/lang_select.html) says the GROUP BY clause accepts [expr]. And [expr] can be composed of a literal. What I cannot find is what SQLite does (or should do) with a literal in the GROUP BY clause. In the simple case of table T1 with two columns C1,C2

Re: [sqlite] SQLite3.Step fast forward / skipping rows

Then when you detect a jump you'll need to use a new search to "jump" to the page you want. Something like SELECT WHERE sortedColumn > lastValue ORDER BY sortedColumn And make sure you have indexes on all the columns that you can sort by. Andy Ling -Original Message- From:

Re: [sqlite] SQLite3.Step fast forward / skipping rows

On 5/24/17, Thomas Flemming wrote: > Hi Ron, > > > there is no system in existence that will do > I was working a lot with Valentina-DB and they have a cursor class: > > var cursor=database.SqlSelect( "SELECT... WHERE... ORDER..."); > > then you can just get any the ListView

Re: [sqlite] SQLite3.Step fast forward / skipping rows

Hi Ron, > there is no system in existence that will do I was working a lot with Valentina-DB and they have a cursor class: var cursor=database.SqlSelect( "SELECT... WHERE... ORDER..."); then you can just get any the ListView wants, forward and backwards, very fast: cursor.Position =

Re: [sqlite] SQLite3.Step fast forward / skipping rows

Whilst this might make writing your application easier, when you think about what has to happen "under the hood" it can't really be any quicker. The database still has to read all the rows that satisfy your WHERE clause and store them somewhere while it sorts them based on your ORDER BY clause,

Re: [sqlite] SQLite3.Step fast forward / skipping rows

On 2017/05/24 3:21 PM, Thomas Flemming wrote: Hi Ron, > there is no system in existence that will do I was working a lot with Valentina-DB and they have a cursor class: var cursor=database.SqlSelect( "SELECT... WHERE... ORDER..."); then you can just get any the ListView wants, forward and

Re: [sqlite] Regarding Sqlite DB Index

On 5/24/17, Manoj Sengottuvel wrote: > > If I create new table (table name : ACCOUNT_MSTR) then the auto index is > created as sqlite_autoindex_ACCOUNT_MSTR_1. > > Then I am trying to execute the following query ' select * from > sqlite_master where type='index';' > > I got

[sqlite] Regarding Sqlite DB Index

Hi Richard, If I create new table (table name : ACCOUNT_MSTR) then the auto index is created as sqlite_autoindex_ACCOUNT_MSTR_1. Then I am trying to execute the following query ' select * from sqlite_master where type='index';' I got the following result type name tbl_name root_page Sql

Re: [sqlite] SQLite3.Step fast forward / skipping rows

On Wednesday, 24 May, 2017 07:21 > > there is no system in existence that will do > I was working a lot with Valentina-DB and they have a cursor class: > > var cursor=database.SqlSelect( "SELECT... WHERE... ORDER..."); > > then you can just get any the ListView wants, forward and backwards,

Re: [sqlite] Regarding Sqlite DB Index

On May 24, 2017 11:33:13 AM EDT, Manoj Sengottuvel wrote: >Hi Richard, > >If I create new table (table name : ACCOUNT_MSTR) then the auto index >is >created as sqlite_autoindex_ACCOUNT_MSTR_1. > >Then I am trying to execute the following query ' select * from >sqlite_master

Re: [sqlite] Group by Literals

On Wednesday, 24 May, 2017 06:07, Denis Burke wrote: > These all produce a single row of output (and it happens to be the last > row > inserted [a1,b5]): > select c1,c2 from t1 group by '1'; > select c1,c2 from t1 group by '2'; > select c1,c2 from t1 group by '3'; > select

Re: [sqlite] Group by Literals

A lot of what we're mentioning is in http://www.sqlite.org/lang_select.html in section 3. For what Keith mentioned below a reminder that min() and max() are special cases where the bare columns are guaranteed to be from same row as (one of the) min or max values. Any other expressions are only

Re: [sqlite] SQLite3.Step fast forward / skipping rows

Like this it works. OK, building the temporary table takes some seconds with a table of 10 mio records, but the queries are really fast then, 10 to 30 ms!! Thanks so much guys for helping me with this :) Tom Am 24.05.2017 um 18:42 schrieb Keith Medcalf: On Wednesday, 24 May, 2017 07:21

Re: [sqlite] sqlite 3.16.3 crash on insert

(1) I cannot reproduce the problem. Can you provide clearer instructions on how to make it misbehave? (2) Why are you using -DSQLITE_OMIT_AUTOINCREMENT? SQLITE_OMIT_AUTOINCREMENT is unsupported and untested. What are you hoping to accomplish by using it? On 5/24/17, Michele Dionisio

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

Have you tried using the command "PRAGMA mmap_size=0;" on the connection? https://www.sqlite.org/pragma.html#pragma_mmap_size -- Joe Mistachkin @ https://urn.to/r/mistachkin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] testfixture.bb ?

Hi sqlite3 gurus ! I'm planning to run the tcl test suite on yocto/qemu and later on a target hw. It seems that I need a testfixture binary also. By chance has anyone a recipe for "sqlite3-testfixture.bb" ? Br, Kalev ___ sqlite-users mailing list

[sqlite] Memory Issue - GLOBs and Windows Active Mapped File

When I'm performing a large amount of selects of GLOBs/thumbnails from an ongoing SQLiteConnection, I'm having a problem where the Windows Active Mapped File will constantly grow out of control in size (memory leak?). This is under Windows 7, using a .Net Framework 4.0 C# application with

[sqlite] sqlite 3.16.3 crash on insert

Hello I have done the same test on sqlite 3.16.3 but I'm quite sure that the same happens on my embedded device that use sqlite 3.19. I say this because I'm debugging the problem on my ubuntu machine where I have sqlite 3.16.3 and I'm able to replicate the issue. I have a database created

Re: [sqlite] UTF8 LIKE stranges

Vlczech - Tomáš Volf wrote: > CREATE TABLE people ( > firstname TEXT, > surname TEXT > ); > INSERT INTO people('Tomáš', 'Surname'); > > "SELECT * FROM people WHERE firstname LIKE ?" > For binding I use: sqlite3_bind_text(stmt, 1, name.c_str(), -1, > SQLITE_STATIC); SQLITE_STATIC works only

[sqlite] SQLite3.Step fast forward / skipping rows

Hi SQLite Users, I have a SELECT query, which returns some 10 records and is displayed in a scrollable ListView. When the user scrolls down the list, each new row is loaded with SQLite3.Step(). The problem is, when the user scrolls fast with the scroll-slider, lots of rows are skipped,