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
