Given:
<code>-- INIT database
CREATE TABLE Result (
ErrorCode INT
);
INSERT INTO Result(ErrorCode) VALUES (500);
INSERT INTO Result(ErrorCode) VALUES (-8000);
INSERT INTO Result(ErrorCode) VALUES (-7777);
INSERT INTO Result(ErrorCode) VALUES (0);
INSERT INTO Result(ErrorCode) VALUES (1000);
</code>
<code>-- INIT database
CREATE TABLE Result (
ErrorCode INT
);
INSERT INTO Result(ErrorCode) VALUES (500);
INSERT INTO Result(ErrorCode) VALUES (-8000);
INSERT INTO Result(ErrorCode) VALUES (-7777);
INSERT INTO Result(ErrorCode) VALUES (0);
INSERT INTO Result(ErrorCode) VALUES (1000);
</code>
-- INIT database
CREATE TABLE Result (
ErrorCode INT
);
INSERT INTO Result(ErrorCode) VALUES (500);
INSERT INTO Result(ErrorCode) VALUES (-8000);
INSERT INTO Result(ErrorCode) VALUES (-7777);
INSERT INTO Result(ErrorCode) VALUES (0);
INSERT INTO Result(ErrorCode) VALUES (1000);
I want to pull records order by the following priority:
<code>1. -7777
2. Lowest negative to -1, incl.
3. Highest positive to 1, incl.
4. Zero
5. Anything else (although there shouldn't be)
</code>
<code>1. -7777
2. Lowest negative to -1, incl.
3. Highest positive to 1, incl.
4. Zero
5. Anything else (although there shouldn't be)
</code>
1. -7777
2. Lowest negative to -1, incl.
3. Highest positive to 1, incl.
4. Zero
5. Anything else (although there shouldn't be)
i.e. that table should be sorted like this:
<code>1. -7777
2. -8000
3. 1000
4. 500
5. 0
</code>
<code>1. -7777
2. -8000
3. 1000
4. 500
5. 0
</code>
1. -7777
2. -8000
3. 1000
4. 500
5. 0
The closest I could get to is this:
<code>SELECT * FROM Result ORDER BY (CASE
WHEN ErrorCode = -7777 THEN 0
WHEN ErrorCode < 0 THEN 1
WHEN ErrorCode > 0 THEN 2
WHEN ErrorCode = 0 THEN 3
ELSE 4
END) ASC;
</code>
<code>SELECT * FROM Result ORDER BY (CASE
WHEN ErrorCode = -7777 THEN 0
WHEN ErrorCode < 0 THEN 1
WHEN ErrorCode > 0 THEN 2
WHEN ErrorCode = 0 THEN 3
ELSE 4
END) ASC;
</code>
SELECT * FROM Result ORDER BY (CASE
WHEN ErrorCode = -7777 THEN 0
WHEN ErrorCode < 0 THEN 1
WHEN ErrorCode > 0 THEN 2
WHEN ErrorCode = 0 THEN 3
ELSE 4
END) ASC;
Which gives me:
<code>1. -7777
2. -8000
3. 500 <-- should be after the next one
4. 1000 <-- should be before the previous one
5. 0
</code>
<code>1. -7777
2. -8000
3. 500 <-- should be after the next one
4. 1000 <-- should be before the previous one
5. 0
</code>
1. -7777
2. -8000
3. 500 <-- should be after the next one
4. 1000 <-- should be before the previous one
5. 0
How do I modify my ORDER BY
, to match my sorting criteria?