Yes. See under item #3 in the Side note on https://sqlite.org/lang_select.html
Special processing occurs when the aggregate function is either min() or max().
Example:
SELECT a, b, max(c) FROM tab1 GROUP BY a;
When the min() or max() aggregate functions are used in an aggregate query, all
bare columns in the result set take values from the input row which also
contains the minimum or maximum. So in the query above, the value of the "b"
column in the output will be the value of the "b" column in the input row that
has the largest "c" value. There is still an ambiguity if two or more of the
input rows have the same minimum or maximum value or if the query contains more
than one min() and/or max() aggregate function. Only the built-in min() and
max() functions work this way.
Many other RDBMS used to do this (ie, Sybase which became MS SQL Server,
amongst others). This was "done away with" in most other SQL implementations
and is indeed "non-standard" behaviour. The standard generally requires that
items in the select list either be aggregates or in the group by list.
This is a specific implementation detail/feature of SQLite3 and will (probably)
not be portable elsewhere ...
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Andy Bennett
>Sent: Wednesday, 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 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 aggregate lead to
>ill-specific or undefined values in those columns. Does SQLite make more
>guarantees than the SQL standard here? Do you have a pointer to the docs
>as
>I tried and failed to find it in there.
>
>
>
>>
>> CREATE TABLE entrys
>> (
>> logid INTEGER NOT NULL,
>> entrynumber INTEGER NOT NULL,
>> region TEXT NOT NULL,
>> key TEXT NOT NULL,
>> timestamp INTEGER NOT NULL,
>> PRIMARY KEY (logid, entrynumber)
>> );
>>
>> CREATE INDEX a on entrys (region, logid, key, entrynumber);
>>
>> SELECT entrys.logid AS logid,
>> max(entrys.entrynumber) AS entrynumber,
>> entrys.region AS region,
>> entrys.key AS key,
>> entrys.timestamp AS timestamp
>> FROM entrys
>> WHERE entrys.region = ?
>> AND entrys.key > ?
>> AND entrys.logid = ?
>> GROUP BY key
>> ;
>>
>> NB: I changed the ill-conceived column names to ones that do
>> not require quoting and the identifier quoted items that are not
>> column names with parameter markers.
>
>
>
>
>
>Best wishes,
>@ndy
>
>--
>andy...@ashurst.eu.org
>http://www.ashurst.eu.org/
>0x7EBA75FF
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
