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
