Sql-server – Select MIN value from multiple columns

sql server

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).

Best Answer

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
;
Related Question