CODEWITHSUNDEEP
sqlsql-server
mgh0104
mgh0104

Reputation: 59

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

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.

Upvotes: -1

Views: 173

Answers (5)

Sunil Nepali
Sunil Nepali

Reputation: 62

This code may work for you.

    SELECT _round, CASE WHEN player_1<player_2 AND player_1<player_3 THEN player_1
                WHEN player_2<player_1 AND player_2<player_3 THEN player_2
                ELSE    player_3 end as LowestValue
            FROM @tbl

Upvotes: -1

DannySlor
DannySlor

Reputation: 4620

In your question you ignored the possibility that two or even three players could share the worst score.
Here is a full solution leveraging cross apply, unpivot and string_agg.

select  * 

from    game 

        cross apply 
        
       (select  min(score)                                             as worst_score
               ,string_agg(player, ',') within group (order by player) as worst_players
               
        from   (select    top 1 with ties 
                          player, score 
        
                from     (select null as dummy) t 
                         unpivot (score for player in (player_1, player_2, player_3)) u
                
                order by  score
                ) u
        ) u
round player_1 player_2 player_3 worst_score worst_players
1 34 28 21 21 player_3
2 42 95 85 42 player_1
3 71 87 71 player_1
4 11 33 11 11 player_1,player_3
5 22 22 22 22 player_1,player_2,player_3

Fiddle

Upvotes: 1

sam
sam

Reputation: 1985

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

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22177

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

Upvotes: 2

Robert Sievers
Robert Sievers

Reputation: 1355

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

Upvotes: -1

Related Questions