'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 |
|---|
