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

(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