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
