Get the highest/lowest value from 2 columns-CodePudding

I've got this table:

  BP1             BP2
 -----------------------------
 140              80
 153              88
 90               162
 98               214
 98               69

I want to organize it into 2 columns: one of high BP and other of low BP:

Expected output:

BP_High        BP_Low
---------------------------------
140             80
153             88
162             90
214             98
98              69

*all values are int

I've tried:

 SELECT CASE WHEN BP1 > BP2 THEN BP1
             WHEN BP1 < BP2 THEN BP2 END BP_High,
        CASE WHEN BP1 > BP2 THEN BP2
             WHEN BP1 < BP2 THEN BP1 END BP_Low 
FROM Table

and it does not work...it gives me all mixed up:

BP_High        BP_Low
---------------------------------
140             80
153             88
90             162
98             214
98              69

I've also tried:

 SELECT CASE WHEN BP1 > BP2 THEN BP1
             ELSE BP2 END BP_High,
        CASE WHEN BP1 > BP2 THEN BP2
             ELSE BP1 END BP_Low
 FROM Table

Is there any other way then CASE WHEN to in order to get the highest/lowest value from 2 columns?

CodePudding user response：

Your third attempt is almost correct, and you in fact can use CASE expressions here:

SELECT
    CASE WHEN BP1 > BP2 THEN BP1 ELSE BP2 END AS BP_High,
    CASE WHEN BP1 < BP2 THEN BP1 ELSE BP2 END AS BP_Low
FROM yourTable;

On other databases, such as MySQL, Postgres, or SQLite, there are LEAST() and GREATEST() scalar functions available. On those databases, your query can be simplified to:

SELECT
    GREATEST(BP1, BP2) AS BP_High,
    LEAST(BP1, BP2) AS BP_Low
FROM yourTable;