Am I doing SQL wrong?

(1) By josh (melbourne_josh) on 2021-06-28 09:46:23

`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]

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 [link]

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]

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]

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