[sqlite] Two different Order By in one statement

[R.Smith]

On 2015-04-01 10:29 AM, Bart Smissaert wrote:
> OK, let me give the simplest example possible.
>
> Table with 3 integer fields, A, B and C
>
> A    B     C
> ----------------------------
> 1     1     2
> 2     1     2
> 1     2     1
> 2     2     1
>
> This needs to be sorted on column A asc
> Then when the value in A is 1 the second sort needs to be asc on column B,
> but when the value in A is 2 then the second sort  needs to be asc on
> column C.
> So, the result should be this:
>
> A    B     C
> ----------------------------
> 1     1     2
> 1     2     1
> 2     2     1
> 2     1     2
>
> I could think of various ways to achieve this, but not with one statement.

SELECT A, B, C, (CASE A WHEN 1 THEN B ELSE C) AS Srt
FROM t WHERE 1
ORDER BY A, Srt DESC;

This might also work according to Darren (thanks for pointing it out) 
and produce one column less, but I did not test it:

SELECT A, B, C
FROM t WHERE 1
ORDER BY A, (CASE A WHEN 1 THEN B ELSE C) DESC;