to:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Hamish Allan
Gesendet: Montag, 03. April 2017 16:24
An: SQLite mailing list
Betreff: Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY
Thanks, Hick. I now understand that it's undefined which value is selected from
the bare column.
t;
> The former returns all groups of c with the top one being the one row
> returned by the latter.
>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Hamish Allan
>> Sent: Monday, 3 April, 2017 0
> contains more than one min() and/or max() aggregate function. Only the
> built-in min() and max() functions work this way."
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
> Auftrag von Hamish Allan
>
DER BY?
>>>>
>>>> The value of a used for the order by is from some random row in the
>>>> grouping of c. Are there relationships between a, b, c, d that you have
>>>> not
>>>> documented nor told us about?
>>>>
>>
being the one row returned
by the latter.
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Hamish Allan
> Sent: Monday, 3 April, 2017 02:51
> To: SQLite mailing list
> Subject: Re: [sqlite] Indexing WHERE with GRO
sers [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Hamish Allan
Gesendet: Montag, 03. April 2017 10:51
An: SQLite mailing list
Betreff: Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY
Ah. My purpose is to determine "d for the most recent c with b=1", with "mo
The order by is useless.
-Original Message-
From: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Hamish Allan
Sent: Sunday, 2 April, 2017 17:28
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Indexing WHERE with GROUP BY and ORDER BY
Given a ta
sqlite.org]
On Behalf Of Hamish Allan
Sent: Sunday, 2 April, 2017 17:28
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Indexing WHERE with GROUP BY and ORDER BY
Given a table:
CREATE TABLE x (a INT, b INT, c TEXT, d TEXT);
the query:
SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a;
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Hamish Allan
>> Sent: Sunday, 2 April, 2017 17:28
>> To: sqlite-users@mailinglists.sqlite.org
>> Subject: [sqlite] Indexing WHERE with GROUP BY and ORDER B
On 3 Apr 2017, at 12:27am, Hamish Allan wrote:
> SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a;
Your problem comes down to this:
If you are GROUPing BY c, why do you want ORDER BY a ?
If you remove the "ORDER BY a" clause then the ideal index would be on (b, c).
But to deal with the ORD
.
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Hamish Allan
> Sent: Sunday, 2 April, 2017 17:28
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Indexing WHERE with GROUP BY and ORDER BY
I had nearly the same question a month ago (Subject: Index usefulness for GROUP
BY). In my case, the best index was on the WHERE clause because it eliminated
the scan and returned only the few important rows for the other clauses.
However, the best result will depend on how many rows are elimi
Given a table:
CREATE TABLE x (a INT, b INT, c TEXT, d TEXT);
the query:
SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a;
shows the following plan, without indexes:
0|0|0|SCAN TABLE x
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY
I can create an index to cover the WH
13 matches
Mail list logo