", and also the value of field2 that corresponds to the maximum field3" <<< now that is useful.
Many thanks. Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 Registered in England and Wales. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: 17 June 2013 17:08 To: General Discussion of SQLite Database Subject: Re: [sqlite] GROUP BY syntax On Mon, Jun 17, 2013 at 12:03 PM, Dave Wellman <dwell...@ward-analytics.com>wrote: > Hi, > > Igor and Richard - thanks for your answers. > > Following up on the example below from Igor, what is the use case ? > SELECT field1, field2, max(field3) FROM table GROUP BY field1; The above returns the maximum field3 for each distinct field1, and also the value of field2 that corresponds to the maximum field3. Another case: SELECT field1, field2, field3 FROM table GROUP BY field1; The above is the same as SELECT DISTINCT ON (field1), field1, field2, field3 FROM table; But SQLite does not support the DISTINCT ON syntax. > > select field1, field2, sum(field3) group by field1; > > If the answer set contains one row per field1 value and an arbitrary > value for field2 - what does this answer provide? > That doesn't really provide you with anything useful, does it? > - especially compared to (say): select field1, sum(field3) group by > field1; > > Cheers, > Dave > > > Ward Analytics Ltd - information in motion > Tel: +44 (0) 118 9740191 > Fax: +44 (0) 118 9740192 > www: http://www.ward-analytics.com > > Registered office address: The Oriel, Sydenham Road, Guildford, > Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 > Registered in England and Wales. > > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik > Sent: 17 June 2013 14:01 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] GROUP BY syntax > > On 6/17/2013 8:36 AM, Dave Wellman wrote: > > So I think that what this is saying is that when you execute an > > aggregate query without a GROUP BY, the chosen non-aggregate values > > are > random (i.e. > > arbitrary). > > This is true with GROUP BY as well - consider: > > select field1, field2, sum(field3) group by field1; > > > Is the above syntax standard ANSI SQL? > > No, it's an extension implemented by SQLite. Most other DBMS produce > an error for such statements. > -- > Igor Tandetnik > > _______________________________________________ > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ 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
