How to find min and max values within a row in SQL?-CodePudding

I have a Numbers table below which contains three numbers (x,y,z):

Numbers table:

 ---- ---- ---- 
| x  | y  | z  |
 ---- ---- ---- 
| 13 | 15 | 30 |
| 10 | 20 | 15 |
 ---- ---- ----

I want to produce a new table which contains three more columns:

min_num: smallest number in the row
second_min_num: second smallest number in the row
max_num: largest number in the row

Example output:

 ---- ---- ---- ---------- ----------------- ---------- 
| x  | y  | z  | min_num  | second_min_num  | max_num  |
 ---- ---- ---- ---------- ----------------- ---------- 
| 13 | 15 | 30 |   13     |        15       |    30    |
| 10 | 20 | 15 |   10     |        15       |    20    |
 ---- ---- ---- ---------- ----------------- ----------

How can I do this in SQL?

CodePudding user response：

We can use LEAST and GREATEST here:

SELECT x, y, z,
       LEAST(x, y, z) AS min_num,
       CASE WHEN x >= LEAST(x, y, z) AND x <= GREATEST(x, y, z) THEN x
            WHEN y >= LEAST(x, y, z) AND y <= GREATEST(x, y, z) THEN y
            ELSE z END AS second_min_num,
       GREATEST(x, y, z) AS max_num
FROM yourTable;

Another version, using subtraction to avoid the CASE expression:

SELECT x, y, z,
       LEAST(x, y, z) AS min_num,
       x   y   z - LEAST(x, y, z) - GREATEST(x, y, z) AS second_min_num
       GREATEST(x, y, z) AS max_num
FROM yourTable;