'SQLite: combination of different ordering policies in one query

I have a table which holds 4 columns: int x, int y, int z, int type.

I need to select sorted list of records when next properties preserved:

All records with type 1 must be ordered by x and y (y is secondary key) in ascending order;

All records of type 2 must be ordered by z. In ascending order. Records of type 2 usually ordered by x and y and when ordered by z the ordering should be the same. In theory. However, in reality some of type 2 records may be partially disordered with respect to z, i.e. for them table.x2 > table.x1 but table.z2 < table.z1.

I need for all selected records of type 2 to ensure table.z2 > table.z1 even at price of having table.x1 > table.x2 (implied, that table.x1 < table.x2 for all other records).

I figured out some query like this:

SELECT * FROM aTable ORDER BY CASE type WHEN 1 THEN x ASC, y ASC ELSE z ASC END;

but for some reason SQLite rejects it as syntactically wrong.

Is it possible anyway to construct such query?

UPDATE:
Example

Data in table

x y z type

1 4 3 1

2 3 6 1

2 2 6 1

8 3 5 2

4 6 3 2

5 8 6 1

7 6 2 2

Expected result:

1 4 3 1

2 2 6 1

2 3 6 1

7 6 2 2

4 6 3 2

5 8 6 1

8 3 5 2



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source