Re: Seemingly inconsistent ORDER BY behavior

On Wed, 2013-08-14 at 14:01 -0400, Richard Hipp wrote:
> CREATE TABLE t1(m VARCHAR(4));
> INSERT INTO t1 VALUES('az');
> INSERT INTO t1 VALUES('by');
> INSERT INTO t1 VALUES('cx');
>
> SELECT '1', substr(m,2) AS m
> FROM t1
> ORDER BY m;
>
> SELECT '2', substr(m,2) AS m
> FROM t1
> ORDER BY lower(m);

You cannot cast your ORDER BY column value.

Instead:-

SELECT '2', LOWER(substr(m,2)) AS m
FROM t1
ORDER BY m;

will have the desired effect.