dbase3 would give an error if you did not include all the non-aggregate fields in the Group By. (One could also step forward/backward in a row-set, so some crude windowing was available if one coded to do that.)
on this: > > select id, category_id, name, min(price) as minprice > > from cat_pictures > > group by category_id; > > I'd be reluctant to write that query because it is non standard SQL and I can't easily (5 minutes of searching) point at a document that tells me the expected behavior. One usually codes to documented behavior because it it is less likely to change without notice. Thanks for the references about windowing functions. Very interesting. The point of what is heavy now vs in 2020 is well made. Is Windowing a Major endeavor, better for sqlite 4? Adam DeVita On Wed, Aug 27, 2014 at 8:25 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > On Wednesday, 27 August, 2014 13:17, Petite Abeille said: > > >On Aug 26, 2014, at 2:09 AM, Keith Medcalf <kmedc...@dessus.com> wrote: > > >> select id, category_id, name, min(price) as minprice > >> from cat_pictures > >> group by category_id; > >> > >> Done. And no need for any windowing functions ... > > >This peculiar behavior is very unique to SQLite. > > Not really. Sybase, SQL Server and DB2 do (or did do) the same thing. > > >Most reasonable SQL engines will throw an exception when confronted > >with the above. SQLite calls it a feature. I personally see it as a > >misfeature. ( Ditto with tagging an implicit limit 1 to scalar > >queries. Anyway. ) > > Well, I kind of like the former (group by) behaviour. Tacking of an > automatic "limit 1" on a scalar subquery may lead one to make bad > assumptions about the shape of one's data, however, if one actually knows > what one is doing, I don't think this is a problem either. > > >On the other hand, one could look at the current 'group by' behavior as > >exhibited by SQLite as a precursor to a proper, more formalize, handling > >of analytic functions.... :) > > Perhaps. On the other hand, I really do not understand why people want > "analytic functions" -- we did perfectly well analyzing data long before > they were invented. But then again I cannot understand why people think > that Relational Databases using SQL are "better" for everything than good > old-fashioned Network-Extended Navigational Databases. But then again, > maybe I'm just an old fart ... > > >_______________________________________________ > >sqlite-users mailing list > >sqlite-users@sqlite.org > >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
