Thanks. That's helpful. "Last visited" would explain that query. But now a new puzzle:
> select col1, col2 from table group by col2 > is identical to select col1, distinct col2 from table The SELECT railroad diagram does not allow DISTINCT in that position, only right after SELECT? Should that be:? select distinct col1, col2 from table 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 Keith Medcalf > Sent: Tuesday, 10 May 2016 12:59 PM > To: SQLite mailing list <sqlite-users at mailinglists.sqlite.org> > Subject: Re: [sqlite] SELECT DISTINCT question > > > 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 > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
