4

I have a very basic database for keeping track of point scores for 6 different events. Each event has 4 events within it.

My Current SQL query is very basic, and looks very similar to what is below;

Select Name, 
       SUM(ISNULL(p.e1_e1_points, 0)+ISNULL(p.e1_e2_points, 0)+ISNULL(p.e1_e3_points, 0)+ISNULL(p.e1_e4_points, 0)) AS Event1, 
       SUM(ISNULL(p.e2_e1_points, 0)+ISNULL(p.e2_e2_points, 0)+ISNULL(p.e2_e3_points, 0)+ISNULL(p.e2_e4_points, 0)) AS Event2,
       SUM(ISNULL(p.e3_e1_points, 0)+ISNULL(p.e3_e2_points, 0)+ISNULL(p.e3_e3_points, 0)+ISNULL(p.e3_e4_points, 0)) AS Event3,
       SUM(ISNULL(p.e4_e1_points, 0)+ISNULL(p.e4_e2_points, 0)+ISNULL(p.e4_e3_points, 0)+ISNULL(p.e4_e4_points, 0)) AS Event4,
       SUM(ISNULL(p.e5_e1_points, 0)+ISNULL(p.e5_e2_points, 0)+ISNULL(p.e5_e3_points, 0)+ISNULL(p.e5_e4_points, 0)) AS Event5,
       SUM(ISNULL(p.e6_e1_points, 0)+ISNULL(p.e6_e2_points, 0)+ISNULL(p.e6_e3_points, 0)+ISNULL(p.e6_e4_points, 0)) AS Event6
       from points

I need to be able to add an additional column, which is the lowest value from columns (being Event1, Event2, Event3, etc).

10

Basically, this is about finding a row-wise minimum.

There is an elegant inline solution in the most upvoted answer to this Stack Overflow question:

According to that solution, if you have a table T of this kind:

C1  C2  C3
--  --  --
…   …   …
…   …   …

you can find a row-wise minimum like this:

SELECT
  C1,
  C2,
  C3,
  (
    SELECT MIN(C)
    FROM (VALUES (C1), (C2), (C3) AS v (C)
  ) AS MinC
FROM
  T
;

Basically you are arranging the values of C1, C2, C3 as a column and are applying a normal (column-wise) aggregate function to it to find the minimum.

Now in your case the C1, C2 etc. are expressions. Usually that is fine, you can use the VALUES row constructor with expressions. But in this case each expression already contains an aggregate function (SUM()), which prevents us from applying the method directly (VALUES expressions must not use aggregate functions).

However, that issue is easily resolved. You can use your current query as a derived table or a CTE and apply the method at the outer level, where the expressions will be just references, like this:

SELECT
  Name,
  Event1,
  Event2,
  Event3,
  Event4,
  Event5,
  Event6,
  (
    SELECT MIN(Event)
    FROM (VALUES (Event1), (Event2), (Event3), (Event4), (Event5), (Event6)) AS v (Event)
  ) AS MinEvent
FROM
  (
    SELECT
      ... /* your current query */
  ) AS derived
;

And here is an identical solution but using a CTE:

WITH cte AS
  (
    SELECT
      ... /* your current query */
  )
SELECT
  Name,
  Event1,
  Event2,
  Event3,
  Event4,
  Event5,
  Event6,
  (
    SELECT MIN(Event)
    FROM (VALUES (Event1), (Event2), (Event3), (Event4), (Event5), (Event6)) AS v (Event)
  ) AS MinEvent
FROM
  cte
;
  • 2
    CROSS APPLY is another option (with either derived table or CTE) – ypercubeᵀᴹ Jul 4 '17 at 10:05
3

I've set up a dbfiddle example simulating your data:

create table events (name varchar(20), event1 int, event2 int, event3 int, event4 int);
insert into events values 
('name1',10,11,12,13),('name2',20,21,22,23),('name3',30,31,32,33);
GO
3 rows affected

IMHO you have some options:

NESTED IIF

-- this is your query
with a as
(
    select name, event1, event2, event3, event4
    from   events
)
select name, event1, event2, event3, event4,
       iif (event1 < event2, event1, 
              iif(event2 < event3, event2, 
                  iif(event3 < event4, event3, event4))) min_event
from a;
GO
name  | event1 | event2 | event3 | event4 | min_event
:---- | -----: | -----: | -----: | -----: | --------:
name1 |     10 |     11 |     12 |     13 |        10
name2 |     20 |     21 |     22 |     23 |        20
name3 |     30 |     31 |     32 |     33 |        30

UNPIVOT

You can use an UNPIVOT solution to get the minimum value in this way:

with a as
(
    select name, event1, event2, event3, event4
    from events
)
, b as
(
    select name, min(event) as min_event
    from   a
    unpivot (event for n in([event1],[event2],[event3],[event4])) upv
    group by name
)
select a.name, a.event1, a.event2, a.event3, a.event4, b.min_event
from   a
join   b
on     b.name = a.name


GO
name  | event1 | event2 | event3 | event4 | min_event
:---- | -----: | -----: | -----: | -----: | --------:
name1 |     10 |     11 |     12 |     13 |        10
name2 |     20 |     21 |     22 |     23 |        20
name3 |     30 |     31 |     32 |     33 |        30

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.