Can you point me to something in the docs? It actually isn't quite the same.
Yes, I have discovered the Sqlite behaviour with aggregate functions. In Postgres and standard SQL it's an error -- the rule is that every column has to be in the GROUP BY if not used in the SELECT list or as input to an aggregate function. Sqlite just punts. I didn't know about DISTINCT. The standard rule here is a bit different: every column in the ORDER BY must be in the select list. Since Andl generates SQL it helps if I know what that SQL will do. Regards David M Bennett FACS Andl - A New Database Language - andl.org > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Hick Gunter > Sent: Monday, 9 May 2016 4:29 PM > To: 'SQLite mailing list' <sqlite-users at mailinglists.sqlite.org> > Subject: Re: [sqlite] SELECT DISTINCT question > > This is documented behaviour for SQLite: > > SELECT a, MAX(b) table; > > Will return (one of) the a value(s) that comes from the same row as the > MAX(b). > > If there are not exactly on of MIN or MAX aggregate functions, SQLite is free > to pick any row (within a group) to return non-aggregated columns from. Thus: > > Select a,SUM(b), c from table group by a; > > will return one of the c values from each group of a values. > > The same thing applies for DISTINCT. > > > -----Urspr?ngliche Nachricht----- > Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] Im Auftrag von Scott Robison > Gesendet: Montag, 09. Mai 2016 08:13 > An: SQLite mailing list > Betreff: Re: [sqlite] SELECT DISTINCT question > > On Sun, May 8, 2016 at 7:45 PM, dandl <david at andl.org> wrote: > > > Just to add to the below: > > > > S# | SNAME | STATUS | CITY > > ------------------------------- > > S1 | Smith | 20 | London > > S2 | Jones | 10 | Paris > > S3 | Blake | 30 | Paris > > S4 | Clark | 20 | London > > S5 | Adams | 30 | Athens > > > > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ; > > > > CITY > > ------ > > Paris > > London > > Athens > > > > I don't find it easy to explain this result. > > > > My guess based on the available data is that, since you don't specify which > "S#" you want associated with each city, it is picking the max of each > (coincidentally). If you want the minimum S# value, this seems to work: > > select distinct city from s group by city order by min("S#") asc; > > I'm not sure if that *should* work per "standard" SQL, but it does with > SQLite. I'd have expected something like this to be necessary: > > select city, min("S#") as x from s group by city order by x asc; > > And if you only want the city: > > select city from (select city, min("S#") as x from s group by city order by x > asc); > > But I'm not a SQL master. > > Distinct used with group by seems redundant, but again, I might just not > understand how they are useful together. > > -- > Scott Robison > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: hick at scigames.at > > This communication (including any attachments) is intended for the use of the > intended recipient(s) only and may contain information that is confidential, > privileged or legally protected. Any unauthorized use or dissemination of > this communication is strictly prohibited. If you have received this > communication in error, please immediately notify the sender by return e-mail > message and delete all copies of the original communication. Thank you for > your cooperation. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
