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

Question

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:

1. min_num: smallest number in the row
2. second_min_num: second smallest number in the row
3. 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?

Answer 1

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;