SQLite Forum

Am I doing SQL wrong?
Login

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

(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!

(3) By Ryan Smith (cuz) on 2021-06-28 11:06:02 in reply to 1 [link] [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.

(5) By Ryan Smith (cuz) on 2021-06-28 11:16:14 in reply to 3 [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