Am I doing SQL wrong?
(1) By josh (melbourne_josh) on 2021-06-28 09:46:23 [link] [source]
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> SELECT column1, column2, column3,
CASE
WHEN column1 > column2 AND column1 < column3 THEN column1
WHEN column2 > column1 AND column2 < column3 THEN column2
WHEN column3 > column1 AND column3 < column2 THEN column3
ELSE -1
END AS only_middle
FROM (VALUES (11, 12, 13),
(21, 12, 13),
(21, 22, 13) );
Expected:
11|12|13|12
21|12|13|13
21|22|13|21
Actual:
11|12|13|12
21|12|13|-1
21|22|13|-1
(2) By Dan Kennedy (dan) on 2021-06-28 10:57:38 in reply to 1 [link] [source]
Seems to be working correctly to me.
Suspect you need another 3 cases in your switch statement:
CASE
WHEN column1 > column2 AND column1 < column3 THEN column1
WHEN column2 > column1 AND column2 < column3 THEN column2
WHEN column3 > column1 AND column3 < column2 THEN column3
WHEN column1 < column2 AND column1 > column3 THEN column1
WHEN column2 < column1 AND column2 > column3 THEN column2
WHEN column3 < column1 AND column3 > column2 THEN column3
ELSE -1
(3) By Ryan Smith (cuz) on 2021-06-28 11:06:02 in reply to 1 [source]
Your expectation is simply wrong.
I will take the second line and demonstrate the three tests for clarity:
Col1 = 21, Col2 = 12 and Col3 = 13. therefore:
Case
Col1 > Col2 (--> True) AND Col1 < Col3 (--> False) : Result: False
Col2 > Col1 (--> False) AND Col2 < Col3 (--> True) : Result: False
Col3 > Col1 (--> False) AND Col3 < Col2 (--> False) : Result: False
That's 3 False evaluations, which leaves the ONLY possible result whatever is in the ELSE, hence returning -1.
Boolean math is at times somewhat hard for humans to intuitively test, but writing down the tests as above might help.
Best of luck.
(4) By josh (melbourne_josh) on 2021-06-28 11:07:46 in reply to 2 [link] [source]
Yes, faulty logic on my part. Thank-you!
(5) By Ryan Smith (cuz) on 2021-06-28 11:16:14 in reply to 3 [link] [source]
Adding to what Dan proposed and what I noted, perhaps another suggestion (which always makes it easier for me to follow) is to use the comparisons in step, meaning the Boolean test:
(B > A) AND (B < C)
is better stated (for at least human intuition) following the mathematics notion of: [ A < B < C ]as:
(A < B) AND (B < C)
This means your CASE could be more intuitively expressed as:
CASE
-- Testing Col1's in-the-middle-ness:
WHEN Col2 < Col1 AND Col1 < Col3 THEN Col1 -- Note: We need 2 tests
WHEN Col3 < Col1 AND Col1 < Col2 THEN Col1
-- Testing Col2's in-the-middle-ness:
WHEN Col1 < Col2 AND Col2 < Col3 THEN Col2
WHEN Col3 < Col2 AND Col2 < Col1 THEN Col2
-- Testing Col3's in-the-middle-ness:
WHEN Col1 < Col3 AND Col3 < Col2 THEN Col3
WHEN Col2 < Col3 AND Col3 < Col1 THEN Col3
ELSE -1 -- Some columns are Equal in this case.
END