SQL: How to take the smallest number in the row?-CodePudding

If there are three game rounds, how do I create a logic where I can find the minimum points per round?

dataset:

round	player_1	player_2	player_3
1	34	28	21
2	42	95	85
3	71	NULL	87

expected result:

round	lowest points per round	worst_player
1	21	player_3
2	42	player_1
3	71	player_1

PS - I have SQL Server 2018. Unfortunately, I am not allowed to use temp tables.

CodePudding user response：

Row Constructor is a simplest way to achieve what is needed.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, _round INT, player_1   INT, player_2 INT, player_3 INT);
INSERT INTO @tbl (_round, player_1, player_2, player_3) VALUES
(1, 34, 28, 21),
(2, 42, 95, 85),
(3, 71, NULL, 87);
-- DDL and sample data population, end

SELECT _round, MIN(c) AS [lowest points per round]
FROM @tbl
CROSS APPLY (VALUES
                (player_1),
                (player_2),
                (player_3)) AS t(c)
GROUP BY _round;

Output

 -------- ------------------------- 
| _round | lowest points per round |
 -------- ------------------------- 
|      1 |                      21 |
|      2 |                      42 |
|      3 |                      71 |
 -------- -------------------------

CodePudding user response：

You can do this through the use of UNPIVOT and windowing functions. I used a temp table to create the values. You would reference your own table. The UNPIVOT function is widely underused. If I were you, I would select from the CTE so that you understand what is happening.

CREATE TABLE #scores (
    num_round INT,
    player_1 INT,
    player_2 INT,
    player_3 INT
)

INSERT INTO #scores
(num_round, player_1, player_2, player_3)
VALUES
(1, 34, 28, 21),
(2, 42, 95, 85),
(3, 71, NULL,87)

;WITH cte AS
(
    SELECT num_round, player_score
    FROM
    (
      SELECT num_round, player_1, player_2, player_3
      FROM #scores
    ) AS x
    UNPIVOT 
    (
      player_score FOR scores IN (player_1, player_2, player_3)
    ) AS up
)

SELECT DISTINCT
    num_round, 
    MIN(player_score) OVER (PARTITION BY num_round) AS minimum_score
FROM cte

CodePudding user response：

You can make use of VALUES (Table Value Constructor) as shown below:

CREATE TABLE #test (
    num_round INT,
    player_1 INT,
    player_2 INT,
    player_3 INT
)

INSERT INTO #test
(num_round, player_1, player_2, player_3)
VALUES
(1, 34, 28, 21),
(2, 42, 95, 85),
(3, 71, NULL,87)


SELECT 
        num_round,
        (select MIN(v) from (values (player_1), (player_2), (player_3)) as value(v) ) as min_score
        FROM #test