"Igor Tandetnik" <itandet...@mvps.org> schrieb im Newsbeitrag news:h584q5$jo...@ger.gmane.org...
> You've truncated the last group short, so a different row from that > "incomplete" group accidentally happened to be chosen. Yep - therefore the recommendation in the VB-newsgroup, to better rely on the Having-clause (performance is not that much an issue in Ricks case). > Try > > SELECT count(*), max(Date) FROM MyTable GROUP BY Year, Week Ah yes - good catch (the Max(Date) term). Although the SQLite-engine behaves relative "stable", regarding "which value is filled into a Column that is part of an Group By-construct", when such a column is specified without an aggregate-expression. Currently! (yes, nothing to rely on) it is the last value, that "reaches the group", probably because the (temporarily used) sorter, which stores the incoming Rows "sorting whilst adding" (according to the Group By-clause), shows a "stable" sort- behaviour, not changing the "first-level, incoming order" which is determined by the RowID of the underlying table itself, as I see it. And Ricks table is built with increasing (auto) RowIDs, "in sync" to the (already sorted) incoming Trading- days/dates - and their "derived" Year, Month, Week, etc. Columns. So I think Rick will not see a difference regarding the reported Date between: SELECT count(*), max(Date) As Date FROM MyTable GROUP BY Year, Week and SELECT count(*), Date FROM MyTable GROUP BY Year, Week or for better comparison: SELECT count(*), max(Date) As MaxDate, Date FROM MyTable GROUP BY Year, Week But without doubt, he should change his current query to the Max(Date) aggregate to be on the safe side. That leads me to a different (somewhat OT-question in the context of *this* thread)... I recently noticed this entry in the SQLite-tktview: http://www.sqlite.org/cvstrac/tktview?tn=3979 And want to implement a fast sorter for SQLite, to become more familiar with the C-language. My question to you experts (including David, who already looked after that ticket) - is there already "work in progress" or were there already attempts, to write at least a "temporary only sorter", which handles all these "throw-away after delivery" Distinct - and Group By, ... etc. cases? Is it possible at all, to write such a thing without tight integration into SQLites VDBE? If yes, is there already an interface-spec available, to handle such temporary sorts with different (pluggable) sort-modules, in a similar way as the interface for e.g. the vfs' was designed and works? If no such interface-spec exists, would it be possible to design such a thing (at least "roughly", by an sqlite-expert) and publish it here or on the sqlite-site, before I start working on that, or do you say: "just be creative!". Any input on that is appreciated - links to already existing attempts or code-snippets too (not meaning the sorting- approach itself, more regarding the integration into SQLite). Regards, Olaf Schmidt _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
