select col1, aggregateFunction(col2) from table group by col3 order by col1
returns the result of the aggregate function applied to all "col2" values in the "col3" group. The col1 value is the last visited row in the group which triggered the aggregate, with a special case for MIN and MAX, where the col1 value is from the last visited row which contained the min or max value of col2. order by is, of course, applied to the result set after it is generated, and may or may not affect the visitation order. select col1, col2 from table group by col2 is identical to select col1, distinct col2 from table in that the table is sorted by col2 and each col2 value is reported only once. The col1 value is from the last visited row in each group. Order by may be applied "after the fact" to order the result set. Order by may or may not affect the visitation order. (Note that "last visited" is often stated as "some random row" because the visitation order is an implementation detail of the query planner and may change from query to query based on the "shape" of the data and the particulars of how the query is solved internally.) > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of dandl > Sent: Monday, 9 May, 2016 18:19 > To: 'SQLite mailing list' > Subject: Re: [sqlite] SELECT DISTINCT question > > The interesting thing about this query is that you can drop any of > DISTINCT, > GROUP BY or ORDER BY and get the same result. > > But my question was not "how can I rewrite my query?". It was: how does > Sqlite interpret this SQL, given that it's probably invalid? > > Andl generates code for both Sqlite and Postgres, and I need to know what > that code does. > > 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 Scott Robison > > Sent: Monday, 9 May 2016 4:13 PM > > To: SQLite mailing list <sqlite-users at mailinglists.sqlite.org> > > Subject: 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 > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
