Hi,
Thanks to everyone who helped with this!
I'll try some stuff out and see if I can get things efficient, fast *and*
simple.
:-)
"There's a small sidenote (that I'm too lazy too find right
now) in the select docs that mentions that, in case of using min
or max as aggregate, the
On 11/20/19 1:26 PM, Simon Slavin wrote:
> On 20 Nov 2019, at 6:11pm, Andy Bennett wrote:
>
>> In past attempts at improving query performance these have been added to
>> encourage it to use an index that it can do a SCAN thru' rather than the
>> table that it would need to do a SEARCH thru'.
>
November, 2019 12:37
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Optimising query with aggregate in subselect.
>
>Hi,
>
>> Did you try retrieving the data "directly" or do you need the
>> subselect in order to maintain compatibility w
"There's a small sidenote (that I'm too lazy too find right now) in the select
docs that mentions that, in case of using min or max as aggregate, the
non-aggregate columns will come from the row that held the min/max value."
Look in
https://www.sqlite.org/quirks.html
under "6. Aggregate
> On 20 Nov 2019, at 20:37, Andy Bennett wrote:
>
> Hi,
>
>> Did you try retrieving the data "directly" or do you need the subselect in
>> order to maintain compatibility with other SQL dialects that are no longer
>> able to retrieve data from the row on which the max was found?
>
> Thanks
Hi,
In past attempts at improving query performance these have
been added to encourage it to use an index that it can do a
SCAN thru' rather than the table that it would need to do a
SEARCH thru'.
SQLite is not using the PRIMARY INDEX to immediately locate the
appropriate row, but is
Hi,
Did you try retrieving the data "directly" or do you need the
subselect in order to maintain compatibility with other SQL
dialects that are no longer able to retrieve data from the row
on which the max was found?
Thanks Keith!
I understood that selecting other columns during an
On
>Behalf Of Andy Bennett
>Sent: Wednesday, 20 November, 2019 09:49
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Optimising query with aggregate in subselect.
>
>Hi,
>
>I'm trying to implement a "streaming" version of the classic "select the
>
On 20 Nov 2019, at 6:11pm, Andy Bennett wrote:
> In past attempts at improving query performance these have been added to
> encourage it to use an index that it can do a SCAN thru' rather than the
> table that it would need to do a SEARCH thru'.
SQLite is not using the PRIMARY INDEX to
Hi,
INNER JOIN "entrys"
ON
1 = "entrys"."log-id" AND
"specific-entrys"."key" = "entrys"."key" AND
"user" = "entrys"."region" AND
"specific-entrys"."entry-number" = "entrys"."entry-number"
AND "entrys"."key" > "G"
I can't solve your problem, but the PRIMARY KEY for "entrys" is
("log-id",
On 20 Nov 2019, at 4:49pm, Andy Bennett wrote:
> INNER JOIN "entrys"
> ON
> 1 = "entrys"."log-id" AND
> "specific-entrys"."key" = "entrys"."key" AND
> "user" = "entrys"."region" AND
> "specific-entrys"."entry-number" = "entrys"."entry-number"
> AND "entrys"."key" > "G"
I can't solve your
Hi,
ORDER BY "key" DESC
This should be ASC, not DESC: I've been working on versions of the query
that can go forwards and backwards and made an editor snafu when writing
the eMail.
Best wishes,
@ndy
--
andy...@ashurst.eu.org
http://www.ashurst.eu.org/
0x7EBA75FF
Hi,
I'm trying to implement a "streaming" version of the classic "select the
latest version of a record" query.
By "streaming" I mean a query that executes by streaming what it needs out
of tables and indexes as it needs it rather than using temporary b-trees or
materializing anything up
13 matches
Mail list logo
