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 non-aggr
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'.
>
, 20 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
"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 Queries
> 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 actual
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 aggregat
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?
CREATE TABLE entrys
(
logid INTEGER NOT NULL,
entrynumber INTEGER NO
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 immedia
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", "entr
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 problem
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
12 matches
Mail list logo
