Hick Gunter gave the documented explanation above. On Mon, May 9, 2016 at 6:18 PM, dandl <david at andl.org> wrote:
> 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 > -- Scott Robison
