Why are you using BOTH distinct and group by on the same column? You only need one or the other. If you are redundantly redundant I would hope that the optimizer makes redundant (as in gets rid of, for those that are not English) the redundancies ...
> -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of dandl > Sent: Sunday, 8 May, 2016 19:45 > To: 'SQLite mailing list' > Subject: Re: [sqlite] SELECT DISTINCT question > > 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. > > 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 dandl > > Sent: Monday, 9 May 2016 11:28 AM > > To: 'SQLite mailing list' <sqlite-users at mailinglists.sqlite.org> > > Subject: [sqlite] SELECT DISTINCT question > > > > I have the following query: > > > > SELECT DISTINCT "EVALA112"("S#") AS "^" FROM "S" ORDER BY "S#" ASC ; > > > > [This is generated code, not hand-written. The table S is from CJ Date > sample > > data.] > > > > This query appears to work correctly. The function is an aggregation, > and > > requires the data to be sorted. > > > > This same query fails in Postgres with: "for SELECT DISTINCT, ORDER BY > > expressions must appear in select list". > > > > In effect the reason is that a query in this form requires two sort > > operations, and a single query can have only one. To get this to work > > correctly, I shall either drop the DISTINCT or turn the second part into > a > > subselect. That I understand. > > > > The question is: how does Sqlite interpret this query? Why is it not an > > error? Is it because the query returns a single result, which does not > > require a sort? > > > > Regards > > David M Bennett FACS > > > > Andl - A New Database Language - andl.org > > > > > > _______________________________________________ > > 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
