On 12/15/2011 11:59 PM, Jeff Matthews wrote:
This is in the documentation:CREATE TABLE t1( x INTEGER PRIMARY KEY, a, /* collating sequence BINARY */ b COLLATE BINARY, /* collating sequence BINARY */ c COLLATE RTRIM, /* collating sequence RTRIM */ d COLLATE NOCASE /* collating sequence NOCASE */ ); /* x a b c d */ INSERT INTO t1 VALUES(1,'abc','abc', 'abc ','abc'); INSERT INTO t1 VALUES(2,'abc','abc', 'abc', 'ABC'); INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc'); INSERT INTO t1 VALUES(4,'abc','abc ','ABC', 'abc'); /* Grouping is performed using the NOCASE collating sequence (Values ** 'abc', 'ABC', and 'Abc' are placed in the same group). */ SELECT count(*) FROM t1 GROUP BY d ORDER BY 1; --result 4 The above example is intuitive to me. But what about this one? /* Grouping is performed using the BINARY collating sequence. 'abc' and ** 'ABC' and 'Abc' form different groups */ SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1; --result 1 1 2 First, if d is collated on NOCASE, then, why does the second operation use BINARY? Is it because of this rule as stated in the documentation: "The collating sequence set by the COLLATE operator overrides the collating sequence determined by the COLLATE clause in a table column definition <http://www.sqlite.org/lang_createtable.html#tablecoldef> ."
It works the same way as ORDER BY: "If the expression is not a column and has no COLLATE clause, then the BINARY collating sequence is used" _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
