Re: [sqlite] ORDER BY is ignored during INSERT INTO

On 25 Aug 2019, at 10:09pm, André Borchert <0xa...@gmail.com> wrote: > I try to copy one table into a second identical one. Once the second table is > created I want to move the content over sorted by ASC. It's worth noting here that the rows of a table do not have any order in SQL. A table

Re: [sqlite] ORDER BY is ignored during INSERT INTO

Which version of SQLite3 are you using? Tip of trunk seems to work correctly ... SQLite version 3.30.0 2019-08-24 20:18:04 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table x(x); sqlite>

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

On Thu, Jun 28, 2018 at 2:03 PM Richard Hipp wrote: > On 6/28/18, Dominique Devienne wrote: > > From reading this list, I've learned that for an index to have a change > to > > be used to consume an order by, the collation of the query and the index > > must match. > > > > But in many

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

On 6/28/18, Dominique Devienne wrote: > From reading this list, I've learned that for an index to have a change to > be used to consume an order by, the collation of the query and the index > must match. > > But in many instances, that index is one from a virtual table we implement. > So is there

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

On Thu, Jun 28, 2018 at 10:59 AM Dominique Devienne wrote: > So is there a way to tell SQLite that vindex is of a given custom > collation, > to open the possibility of the index being used? > Note that there's no mention at all of "collation" or "collate" in https://www.sqlite.org/vtab.html so

Re: [sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

No. Column name and table referents (identifiers) must be specified explicitly (as part of the command) and MUST NOT be bound parameters. You are asking to sort by the value 1 for all rows, which means that the output is in "visitation order" since the ORDER BY value is the same for all rows

Re: [sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

> On Jan 29, 2018, at 11:13 AM, Danny Milosavljevic > wrote: > > Should this use case work? Nope. The ORDER BY clause can affect the query plan and the generated bytecode, so it's not something you can change in a binding. You have to compile a new statement with a

Re: [sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

Danny Milosavljevic wrote: > I'm trying to prepare a statement for "SELECT a FROM t ORDER BY ?" and > then sqlite3_bind_int the parameter to 1 (on sqlite 3.19.3). > > Expected result: Orders result by column "a", in ascending order. > Observed result: Orders in some strange order. Ordering by

Re: [sqlite] Order By gives different result

On 08/21/2017 11:51 PM, Stephen Chrzanowski wrote: I think I may have found a query issue. I haven't checked the SQLite docs to see if this is something that is specified as an order of operations, or if this is a check that has been overlooked. I think using a column alias in a complex

Re: [sqlite] Order of fields for insert

All I/O is done via page sized blocks. So the minimum amount of data to be fetched will always be a page. The bigger issue is, as you said, when you need to follow a chain of pages to get a small value at the end. -j On Thu, Oct 6, 2016 at 9:53 AM, Paul Sanderson

Re: [sqlite] Order of fields for insert

On Thu, Oct 6, 2016 at 9:25 AM, Hick Gunter wrote: > SQLite compresses rows before storing and decompresses rows before > returning fields. BLOB fields are the most time consuming to process and so > should be placed at the end of the row. Often used fields - i.e. (foreign) >

Re: [sqlite] Order of fields for insert

> Long columns, especially TEXT or BLOBs which may have lots of data in, should > go at the end. Because you don't want SQLite to have to fetch all that data > from storage just to get at the column after it. To be pedantic SQLite does not need to "fetch" all of the data from strorage before

Re: [sqlite] Order of fields for insert

ok On 6 Oct 2016 8:08 p.m., "Simon Slavin" wrote: > > On 6 Oct 2016, at 3:37pm, Krishna Shukla > wrote: > > > Help how can i import exel file in sqlite and get result in c# desktop > > application ...? > > Please start a new thread for this

Re: [sqlite] Order of fields for insert

On 6 Oct 2016, at 2:46pm, Jeff Archer wrote: > Are there any performance or other considerations of the order of the > fields for an insert? No. Order of columns in the CREATE TABLE command matters. Order they're named in operations after that doesn't. When SQLite

Re: [sqlite] Order of fields for insert

On 6 Oct 2016, at 3:37pm, Krishna Shukla wrote: > Help how can i import exel file in sqlite and get result in c# desktop > application ...? Please start a new thread for this question. Simon. ___ sqlite-users mailing

Re: [sqlite] Order of fields for insert

Help how can i import exel file in sqlite and get result in c# desktop application ...? On 6 Oct 2016 7:55 p.m., "Hick Gunter" wrote: > SQLite compresses rows before storing and decompresses rows before > returning fields. BLOB fields are the most time consuming to process and

Re: [sqlite] Order of fields for insert

SQLite does not use any compression when storing data. Occasionally rows have so much data that they overflow to an additonal page(s) so the advice about defining tables so that blobs are at the end of the definition is good - also columns that store long strings might be better at the end of a

Re: [sqlite] Order of fields for insert

SQLite compresses rows before storing and decompresses rows before returning fields. BLOB fields are the most time consuming to process and so should be placed at the end of the row. Often used fields - i.e. (foreign) key fields - should be placed at the front of the row. This will help most if

Re: [sqlite] Order of fields for insert

Sorry, that was just mistake in reducing the code for the email, please ignore. What do you mean "what matters is order of columns in table"? or was that just referring to the typo? Jeff Archer jeffarch...@gmail.com On Thu, Oct 6, 2016 at 9:52 AM, Clemens Ladisch

Re: [sqlite] Order of fields for insert

Jeff Archer wrote: > Are there any performance or other considerations of the order of the > fields for an insert? No; what matters is the order of columns in the table. > INSERT INTO ​mytable(wid1,cnt,​dat,​wid3,wid2) VALUES (?,?,?,?) ​> - VS - ​ > INSERT INTO ​mytable(wid1,wid2,wid3,cnt​,dat​)

Re: [sqlite] Order of applying subquery in SELECT statement

> On Mon, Jun 20, 2016 at 5:18 PM, Keith Medcalf > wrote: > > > > > You have all ill-phrased correlated subquery. Only by putting c.id in > > the select list and adding c.id to the group go do you have a properly > > phrased correlated subquery. > > > > However, > > > >

Re: [sqlite] Order of applying subquery in SELECT statement

On 21 Jun 2016, at 1:31am, J Decker wrote: > as long as the dataset > is exactly the same or is modified in ways that don't break the index > lookup ordering you'll be 'stable' Or you upgrade to a new version of SQLite which optimizes the query differently. Simon.

Re: [sqlite] Order of applying subquery in SELECT statement

On Mon, Jun 20, 2016 at 5:18 PM, Keith Medcalf wrote: > > You have all ill-phrased correlated subquery. Only by putting c.id in > the select list and adding c.id to the group go do you have a properly > phrased correlated subquery. > > However, > > The projection (join)

Re: [sqlite] Order of applying subquery in SELECT statement

You have all ill-phrased correlated subquery. Only by putting c.id in the select list and adding c.id to the group go do you have a properly phrased correlated subquery. However, The projection (join) will be performed first using whatever order and indexes the optimizer decides to use to

Re: [sqlite] Order of applying subquery in SELECT statement

On 6/20/2016 3:32 PM, R Smith wrote: SELECT a.id, MAX(b.orderno) as maxorderno FROM a INNER JOIN c on c.a_id = a.id INNER JOIN b ON b.c_id = c.id GROUP BY a.id; -- | maxorder- --- This is what I think you might actually want, -- id | no --- it

Re: [sqlite] Order of applying subquery in SELECT statement

On 2016/06/20 5:37 PM, Harmen de Jong - CoachR Group B.V. wrote: I have a query with a subquery (using an aggregate function) in the SELECT statement. Will this subquery be executed before or after the GROUP BY? For example: SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as

Re: [sqlite] Order of applying subquery in SELECT statement

On 6/20/2016 11:37 AM, Harmen de Jong - CoachR Group B.V. wrote: I have a query with a subquery (using an aggregate function) in the SELECT statement. Will this subquery be executed before or after the GROUP BY? For example: SELECT a.id, (SELECT max(orderno) FROM b WHERE b.c_id=c.id) as

Re: [sqlite] Order of applying subquery in SELECT statement

On Mon, Jun 20, 2016 at 8:37 AM, Harmen de Jong - CoachR Group B.V. < har...@coachr.com> wrote: > I have a query with a subquery (using an aggregate function) in the SELECT > statement. Will this subquery be executed before or after the GROUP BY? > For example: > SELECT a.id, (SELECT max(orderno)

Re: [sqlite] ORDER BY and LIMIT regression

> On 1/19/15, Angelo Mottola converge.it> > wrote: >> Hello, >> >> I have a regression to report, that seems to have been introduced between >> SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6 >> and stopped working somewhere in 3.8.7.x; we were

Re: [sqlite] ORDER BY and LIMIT regression

On Mon, 19 Jan 2015, Richard Hipp wrote: Thank you for reporting the problem. Certainly! We always fix every problem that we are aware of in SQLite. But this problem had not been previously reported to us, and did not occur in any of the 168 million test cases that we ran prior to

Re: [sqlite] ORDER BY and LIMIT regression

On 1/19/15, Angelo Mottola wrote: > Hello, > > I have a regression to report, that seems to have been introduced between > SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6 > and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in >

Re: [sqlite] ORDER BY and LIMIT regression

On 1/19/15, Simon Slavin wrote: > > On 19 Jan 2015, at 3:10pm, Richard Hipp wrote: > >> It is a very >> complex problem. In particular, the sample query works fine as long >> as the number of columns in the result set is not exactly 60. Adding >> or

Re: [sqlite] ORDER BY and LIMIT regression

On 19 Jan 2015, at 3:10pm, Richard Hipp wrote: > It is a very > complex problem. In particular, the sample query works fine as long > as the number of columns in the result set is not exactly 60. Adding > or removing a single column of result gives the correct answer. I

Re: [sqlite] ORDER BY and LIMIT regression

On 1/19/15, RSmith wrote: > Might this not be a "reverse_unordered_selects" pragma or compile option > going wrong, or at least the code making it work getting > somehow hooked in the new versions for this query? > It looks like a bug. If you update to the latest trunk

Re: [sqlite] ORDER BY and LIMIT regression

Might this not be a "reverse_unordered_selects" pragma or compile option going wrong, or at least the code making it work getting somehow hooked in the new versions for this query? I have seen similar things when using that pragma (but of course that was intended). Just a thought... On

Re: [sqlite] ORDER BY and LIMIT regression

Ignore my previous email on this subject. We are able to get different results from 3.8.6 and 3.8.8. Unclear yet if the one or the other is incorrect. On 1/19/15, Richard Hipp wrote: > On 1/19/15, Angelo Mottola wrote: >> Hello, >> >> I have a

Re: [sqlite] ORDER BY and LIMIT regression

On 1/19/15, Angelo Mottola wrote: > Hello, > > I have a regression to report, that seems to have been introduced between > SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6 > and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in >

Re: [sqlite] Order Statistic Trees

On Sat, May 17, 2014 at 9:41 PM, Scott Robison wrote: > On May 17, 2014 8:40 PM, "Simon Slavin" wrote: > > Just want to check: this is what it sounds like, right ? Your user has > a window open where they are scrolling through the table, ordered by

Re: [sqlite] Order Statistic Trees

Sorry for the incomplete post, stupid phone. On May 17, 2014 8:40 PM, "Simon Slavin" wrote: > Just want to check: this is what it sounds like, right ? Your user has a window open where they are scrolling through the table, ordered by SomeColumn. You need to know whether

Re: [sqlite] Order Statistic Trees

Sorry, I typoed the second query. Should have been <=. On May 17, 2014 8:40 PM, "Simon Slavin" wrote: > > On 18 May 2014, at 3:19am, Scott Robison wrote: > > > It is easy enough to read the source data and insert it into a table. It > is > > easy

Re: [sqlite] Order Statistic Trees

On 18 May 2014, at 3:19am, Scott Robison wrote: > It is easy enough to read the source data and insert it into a table. It is > easy enough to query the list of data ordered by any field after the data > has been completely read. The hard part is knowing the correct

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

Federal University Oye Ekiti conduct Third Matriculation this February visit http://www.fuoye.edu.ng for more details -- View this message in context: http://sqlite.1065341.n5.nabble.com/ORDER-BY-issue-v3-8-2-amalgamtion-tp73605p73704.html Sent from the SQLite mailing list archive at

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

Hello Simon, Clemens, and Richard, Thank you for your help. > Simon: "What are you seeing from your SELECT that you weren't expecting ?" 0 | Batt 0 | ClockBatt 0 | Batt 0 | BP 0 | ORP 0 | Ref 0 | pH 0 | pH 0 | DO ... > Simon: "Can you reduce your INSERTs to just two rows, and still get

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

On Sun, Feb 2, 2014 at 11:58 PM, Labar, Ken wrote: > > > > This used to work until we upgraded sqlite to v3.8.2. It still does work at the PC level. And earlier: > - IAR C compiler Can you try recompiling with all compiler optimizations turned off and see if you

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

Labar, Ken wrote: > [...] > select (rtTableSort < 1000) as selected, abbrString from userParameter order > by abbrString; > > This used to work until we upgraded sqlite to v3.8.2. It still does work at > the PC level. What is the EXPLAIN QUERY PLAN output for this query on the handheld? When

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

On 3 Feb 2014, at 4:58am, Labar, Ken wrote: > select (rtTableSort < 1000) as selected, abbrString from userParameter order > by abbrString; > > > This used to work until we upgraded sqlite to v3.8.2. It still does work at > the PC level. Thanks for test data and SELECT

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

Hello Simon, Below is the query, and the schema I’m trying to debug. FYI: I’m signed up for the daily digest, so please CC kla...@hach.com on replies for a faster response. CREATE TABLE userParameter ( hepi_parameter_id INT NOT NULL

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

On 1 Feb 2014, at 3:53am, Labar, Ken wrote: > Upgrading from 3.7.14.1 to 3.8.2 our previously sorted queries are no > longer sorted. > Testing this same database with the win7 binary 3.8.2 the query is correctly > sorted. Does your query include an ORDER BY clause ? If

Re: [sqlite] order of = in join

Anbrus wrote: "what's the easiest way to generate a set of fixed number of rows like VALUES does but in an order of your choice?" Order them before with the command 'sort' NAME sort - sort lines of text files SYNOPSIS sort [OPTION]... [FILE]... DESCRIPTION Write sorted

Re: [sqlite] Order of rows in a VALUES select expression

I am not in front of a sqlite equipped system at the moment, but wouldn't something like this probably work? select * from (values ...) order by ... On Thu, Jan 30, 2014 at 1:28 PM, Zsbán Ambrus wrote: > The future release sqlite 3.8.3 adds VALUES statements as a shortcut

Re: [sqlite] order of = in join

What version of SQLite and what indexes have you created? (and, if you have relevant indexes, have you run analyze?) >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of E. Timothy Uy >Sent: Thursday, 30 January, 2014 09:25

Re: [sqlite] order of = in join

On 1/30/14, E. Timothy Uy wrote: > #1 - f.term = t.term > Query #1 takes 300 ms, and query #2 takes 30 ms. Can you show the schema for the tables? Is it possible that the two equals comparisons use different affinity or collation, which changes the semantics of your statement?

Re: [sqlite] order of = in join

E. Timothy Uy wrote: > Here the Terms_content table is big, maybe 300k rows, while the Favorites > table is small, <100 rows. Query #1 takes 300 ms, and query #2 takes 30 ms. Which SQLite version? What is the output of EXPLAIN QUERY PLAN for both queries? Regards, Clemens

Re: [sqlite] order of = in join

On 30 Jan 2014, at 4:24pm, E. Timothy Uy wrote: > Here the Terms_content table is big, maybe 300k rows, while the Favorites > table is small, <100 rows. Query #1 takes 300 ms, and query #2 takes 30 ms. Have you run ANALYZE on the database ? Simon.

Re: [sqlite] order by col desc limit 1 logically equivalent to max(col)

On Thu, 19 Dec 2013 15:14:24 +0100 Dominique Devienne wrote: > The selling point of SQL is to declaratively tell the engine what you > need, and let it choose the optimal implementation. So saying that > ORDER BY doesn't know about LIMIT as a matter of fact seems >

Re: [sqlite] order by col desc limit 1 logically equivalent to max(col)

On Thu, Dec 19, 2013 at 12:34 PM, Clemens Ladisch wrote: > Dominique Devienne wrote: > > In the two queries below, there's a 5x performance difference. > > > select max(id) from t100m limit 1; > > select id from t100m order by id desc limit 1; > > > But logically, it seems to

Re: [sqlite] order by col desc limit 1 logically equivalent to max(col)

Dominique Devienne wrote: > In the two queries below, there's a 5x performance difference. > select max(id) from t100m limit 1; > select id from t100m order by id desc limit 1; > But logically, it seems to me that the limit 1 on the order by is logically > equivalent to a min or max depending on

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

Thanks. I think using GROUP BY without aggregates is a strange way to remove duplicates, anyway. Not intentional. SQLite simply fails to recognize that by using the GROUP BY in descending order it could avoid the ORDER BY clause. This is an optimization that we have never considered because it

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

Fabian Büttner wrote: > Hi, > > I have been thinking about a question on stackoverflow > (http://stackoverflow.com/questions/19236363/select-distinct-faster-than-group-by), > where some SQL framework removes duplicates from results using GROUP BY > instead of DISTINCT. > I don't want to discuss

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

On Mon, Oct 21, 2013 at 5:27 AM, Fabian Büttner wrote: > Hi, > > I have been thinking about a question on stackoverflow ( > http://stackoverflow.com/**questions/19236363/select-** >

Re: [sqlite] Order By clause in aggregate functions?

On 21 Jul 2013, at 11:47pm, Igor Tandetnik wrote: > On 7/21/2013 5:01 PM, Simon Slavin wrote: >> I had to fake it. The parameter I passed to my aggregate function was a >> string as follows: >> >> theOrder||':'||theValue >> >> My function extension had to split the

Re: [sqlite] Order By clause in aggregate functions?

On 7/21/2013 5:01 PM, Simon Slavin wrote: I had to fake it. The parameter I passed to my aggregate function was a string as follows: theOrder||':'||theValue My function extension had to split the values into two parts Couldn't you just pass two parameters, separately? -- Igor Tandetnik

Re: [sqlite] Order By clause in aggregate functions?

On Sun, Jul 21, 2013 at 1:29 PM, Petite Abeille wrote: > > On Jul 21, 2013, at 10:15 PM, ss griffon wrote: > > > I'm writing an extension to SQLite that adds some aggregate functions. > > Some of them, require that the rows passed to the

Re: [sqlite] Order By clause in aggregate functions?

On 21 Jul 2013, at 9:29pm, Petite Abeille wrote: > Short answer: no. Right. I had to fake it. The parameter I passed to my aggregate function was a string as follows: theOrder||':'||theValue My function extension had to split the values into two parts, then sort

Re: [sqlite] Order By clause in aggregate functions?

On Jul 21, 2013, at 10:15 PM, ss griffon wrote: > I'm writing an extension to SQLite that adds some aggregate functions. > Some of them, require that the rows passed to the aggregate function > be sorted. It seems as if lots of data bases (MySQL, PostgreSQL) > support

Re: [sqlite] ORDER of export on a SELECT

Thanks for the advice, both. In the mean time we know that it indeed something else causing the problem. gert 2013/5/2 Simon Slavin > > On 2 May 2013, at 9:26am, Gert Van Assche wrote: > > > I have a table with 2 fields that need to be exported to 2

Re: [sqlite] ORDER of export on a SELECT

On 2 May 2013, at 9:26am, Gert Van Assche wrote: > I have a table with 2 fields that need to be exported to 2 TXT files but > the order of the lines in the export should be exactly the same as in the > table. Tables do not have order. Really. A table is a set of rows,

Re: [sqlite] ORDER of export on a SELECT

On Thu, May 2, 2013 at 4:26 AM, Gert Van Assche wrote: > All, I > > I have a table with 2 fields that need to be exported to 2 TXT files but > the order of the lines in the export should be exactly the same as in the > table. > I tried to do it like this: > SELECT

Re: [sqlite] Order of columns in group by statement affects query performance

On Fri, 26 Apr 2013 11:12:15 +0200 Hick Gunter wrote: > It is not unreasonable to assume that in a well designed SQL > Statement the GROUP BY clause will be backed up by the necessary > index and an identical ORDER BY clause That is an entirely unreasonable assumption. Order

Re: [sqlite] Order of columns in group by statement affects query performance

r GROUP BY** clauses of the query, but also from any ON or USING clauses if the query is a join. -Ursprüngliche Nachricht- Von: James K. Lowden [mailto:jklow...@schemamania.org] Gesendet: Donnerstag, 25. April 2013 16:34 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Order of columns in group

Re: [sqlite] Order of columns in group by statement affects query performance

On 25 Apr 2013, at 4:23pm, Jay A. Kreibich wrote: > Except there is no such thing as "GROUP BY order". SQL Golden Rule: > If there is no ORDER BY, the rows have no order. According to SQL, > neither the groups, nor the rows within a group (as they are fed into >

Re: [sqlite] Order of columns in group by statement affects query performance

On Thu, Apr 25, 2013 at 05:08:04PM +0200, Daniel Winter scratched on the wall: > 2013/4/25 James K. Lowden > > > > > Nonsense. The query parser sees GROUP BY A,B. The optimizer sees an > > index ordered B,A. By permuting the order of the columns in the GROUP > > BY

Re: [sqlite] Order of columns in group by statement affects query performance

On Thu, Apr 25, 2013 at 10:29:34AM +0200, Hick Gunter scratched on the wall: > AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from hints > in the virtual table description). They're not the same clause, they don't do the same thing. Now, it is true that most database systems

Re: [sqlite] Order of columns in group by statement affects query performance

2013/4/25 James K. Lowden > > Nonsense. The query parser sees GROUP BY A,B. The optimizer sees an > index ordered B,A. By permuting the order of the columns in the GROUP > BY clause, it finds a match for the index and uses it. > > Yes, the problem is O(n^2), where n

Re: [sqlite] Order of columns in group by statement affects query performance

On Thu, 25 Apr 2013 10:29:34 +0200 Hick Gunter wrote: > AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from > hints in the virtual table description). That might be so, in some limited sense. It's obviously false in general because they mean different things and

Re: [sqlite] Order of columns in group by statement affects query performance

. This would probably require code paths presumably shared by GROUP BY and ORDER BY processing to be split. -Ursprüngliche Nachricht- Von: James K. Lowden [mailto:jklow...@schemamania.org] Gesendet: Donnerstag, 25. April 2013 01:55 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Order

Re: [sqlite] Order of columns in group by statement affects query performance

On Wed, 24 Apr 2013 17:46:00 +0100 Simon Slavin wrote: > On 24 Apr 2013, at 5:14pm, Igor Tandetnik wrote: > > Note though that the query doesn't have an ORDER BY clause. It > > doesn't request rows in any particular order. SQLite could, in > >

Re: [sqlite] Order of columns in group by statement affects query performance

On 24 Apr 2013, at 5:14pm, Igor Tandetnik wrote: > Note though that the query doesn't have an ORDER BY clause. It doesn't > request rows in any particular order. SQLite could, in principle, reorder > columns in GROUP BY to take advantage of the index. I suppose the

Re: [sqlite] Order of columns in group by statement affects query performance

On 4/24/2013 11:49 AM, Larry Brasfield wrote: *Daniel Winter wrote:0* Table: Column A int, Column B int, Column C int One Index: A,B (combined) Query 1: SELECT A,B,count(*) from tableTest group by A,B Query 2: SELECT A,B,count(*) from tableTest group by B,A Query 1 will use the index,

Re: [sqlite] Order of columns in group by statement affects query performance

*Daniel Winter wrote:0* > I discovered that the order of columns in a group by affects the > performance of a query. Is this expected? Yes. > For example: > > Table: Column A int, Column B int, Column C int > One Index: A,B (combined) > > Query 1: SELECT A,B,count(*) from tableTest group by

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

This issue is now recorded as http://www.sqlite.org/src/info/2500cdb9be On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle wrote: > I have a problem with a field name being ambiguous when it wasn't before. > Is this a bug or a feature? > > Firefox 19 uses Sqlite 3.7.14.1 which does

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

On Apr 11, 2013, at 5:56 AM, Stephen Chrzanowski wrote: > Looking at the query, in the CLI, what if you rename the three bolded > "title" fields to something else? I think the system is getting messed up > because a field in a table is called "title" and you're naming a

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

Looking at the query, in the CLI, what if you rename the three bolded "title" fields to something else? I think the system is getting messed up because a field in a table is called "title" and you're naming a query called "title" but you're not distinctly stating which "title" you want to query

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

On Apr 10, 2013, at 12:40 PM, Richard Hipp wrote: > On Wed, Apr 10, 2013 at 3:37 PM, Perry Wagle wrote: > >> >> On Apr 10, 2013, at 5:53 AM, Stephen Chrzanowski >> wrote: >> >>> Has the schema changed on one of the underlying table

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

On Wed, Apr 10, 2013 at 3:37 PM, Perry Wagle wrote: > > On Apr 10, 2013, at 5:53 AM, Stephen Chrzanowski > wrote: > > > Has the schema changed on one of the underlying table between 19 and 20 > > that includes a field called Title? > > I did the query (see my

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

On Apr 10, 2013, at 5:53 AM, Stephen Chrzanowski wrote: > Has the schema changed on one of the underlying table between 19 and 20 > that includes a field called Title? I did the query (see my previous response) with a different version of sqlite3 (3.7.16.1) on a places

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

On Apr 10, 2013, at 5:33 AM, Richard Hipp wrote: > On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle wrote: > >> I have a problem with a field name being ambiguous when it wasn't before. >> Is this a bug or a feature? >> >> Firefox 19 uses Sqlite 3.7.14.1 which

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

Has the schema changed on one of the underlying table between 19 and 20 that includes a field called Title? On Wed, Apr 10, 2013 at 8:33 AM, Richard Hipp wrote: > On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle wrote: > > > I have a problem with a field name being

Re: [sqlite] ORDER BY finds "AS" names ambiguous that shouldn't be?

On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle wrote: > I have a problem with a field name being ambiguous when it wasn't before. > Is this a bug or a feature? > > Firefox 19 uses Sqlite 3.7.14.1 which does what it should. Firefox 20 > uses Sqlite 3.7.15.2 which complains that

Re: [sqlite] Order of ON DELETE CASCADE specified in SQLite?

On Wed, Mar 13, 2013 at 7:36 PM, Dan Kennedy wrote: > On 03/14/2013 12:09 AM, Dominique Devienne wrote: >> I stumbled upon >> http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processed >> >> Is this just happenstance in this case or

Re: [sqlite] Order of ON DELETE CASCADE specified in SQLite?

On 03/14/2013 12:09 AM, Dominique Devienne wrote: I stumbled upon http://stackoverflow.com/questions/60168/in-what-order-are-on-delete-cascade-constraints-processedand tried a simplified version in SQLite3, which appears to work fine (see below). Is this just happenstance in this case or will

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

On 15 Jan 2013, at 1:58pm, Ryan Johnson wrote: > On 14/01/2013 9:48 AM, François-xavier Jacobs wrote: >> i would like to "seed random" a request, so i could do use some pagination >> system with a order by random(), is this possible sqlite ? when a tried to >> use

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

Indeed shuffle was my goal i solve it by doing order by "((rowid * "+seed.longValue()+" ) % "+BIG_PRIME_NUMBER+")"; as long ass the seed doe not change result is the same, so i can shuffle with LIMT Regards 2013/1/15 Ryan Johnson > On 14/01/2013 9:48 AM,

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

François-xavier Jacobs wrote: > when a tried to use "order by 1234567892" it always return the same order Because this is the *same* value for all records. > i would like to "seed random" a request, so i could do use some pagination > system with a order by random(), is this possible sqlite ?

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

On 1/14/2013 9:48 AM, François-xavier Jacobs wrote: i would like to "seed random" a request, so i could do use some pagination system with a order by random(), is this possible sqlite ? when a tried to use "order by 1234567892" it always return the same order random() returns a different value

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

If you literally used "ORDER BY 1234567892" then there's nothing in the record being sorted. I can't recall is SQLite allows order by aliases, but something like.. Select ..., random() as X Order by X Might work, as long as random() is executed for each row. (Sorry.. don't have a quick SQLite

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

On 14/01/2013 9:48 AM, François-xavier Jacobs wrote: Hi everyone i would like to "seed random" a request, so i could do use some pagination system with a order by random(), is this possible sqlite ? when a tried to use "order by 1234567892" it always return the same order That's because you

Re: [sqlite] order by clause should come after union not before

select * from (select * from hbc order by cmc desc limit 10) as ss union select * from hbc where qph>0 or hctl=1; --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -Original Message- > From:

Re: [sqlite] order by clause should come after union not before

YAN HONG YE wrote: > select * from hbc order by cmc desc limit 10 > union > select * from hbc where qph>0 > union > select * from hbc where hctl=1 > > this sql cmd cause the error: > order by clause should come after union not before select * from hbc where rowid in

Re: [sqlite] order by clause should come after union not before

On Fri, Jun 15, 2012 at 08:10:20AM -0400, Kevin Benson scratched on the wall: > On Fri, Jun 15, 2012 at 5:56 AM, YAN HONG YE wrote: > > > > select * from hbc order by cmc desc limit 10 > > union > > select * from hbc where qph>0 > > union > > select * from hbc where hctl=1 >

  1   2   3   >