On 7 May 2013, at 2:35am, Nico Williams <n...@cryptonector.com> wrote:
> I read this: > > SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a; > > as saying "select all a from t1 that are not in t2, *then* order that > by that a". > > I read this: > > SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase; > > to mean "select a from t1 that are not in t2, *then* order that by a > with the nocase collation". > > I don't understand why the COLLATE clause on the ordering term should > be applied transitively to the sub-expressions in the select instead > of only to the result set of the select. The problem, I think, is that a COLLATE shouldn't change any value anyhow, no matter which SELECT the ORDER clause is attached to. The COLLATE modifier is part of the ORDER BY clause. It is there to change the ORDER that the values are returned in, not the values themselves. And something like SELECT x EXCEPT y is subtracting one set from another, and in sets the order doesn't matter. The problem is something like doing SELECT words FROM dictionary ORDER BY words COLLATE NOCASE and getting all the words back as capital letters. This shouldn't happen. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
