Row-wise maximum in T-SQL [duplicate]

问题:

This question already has an answer here:

• SQL MAX of multiple columns? 22 answers

I've got a table with a few columns, and for each row I want the maximum:

-- Table:
+----+----+----+----+----+
| ID | C1 | C2 | C3 | C4 |
+----+----+----+----+----+
|  1 |  1 |  2 |  3 |  4 |
|  2 | 11 | 10 | 11 |  9 |
|  3 |  3 |  1 |  4 |  1 |
|  4 |  0 |  2 |  1 |  0 |
|  5 |  2 |  7 |  1 |  8 |
+----+----+----+----+----+


-- Desired result:
+----+---------+
| ID | row_max |
+----+---------+
|  1 |       4 |
|  2 |      11 |
|  3 |       4 |
|  4 |       2 |
|  5 |       8 |
+----+---------+

With two or three columns, I'd just write it out in iif or a CASE statement.

select ID
  , iif(C1 > C2, C1, C2) row_max
from table

But with more columns this gets cumbersome fast. Is there a nice way to get this row-wise maximum? In R, this is called a "parallel maximum", so I'd love something like

select ID
  , pmax(C1, C2, C3, C4) row_max
from table

回答1:

What about unpivoting the data to get the result? You've said tsql but not what version of SQL Server. In SQL Server 2005+ you can use CROSS APPLY to convert the columns into rows, then get the max value for each row:

select id, row_max = max(val)
from yourtable
cross apply
(
  select c1 union all
  select c2 union all
  select c3 union all
  select c4
) c (val)
group by id

See SQL Fiddle with Demo. Note, this could be abbreviated by using a table value constructor.

This could also be accomplished via the UNPIVOT function in SQL Server:

select id, row_max = max(val)
from yourtable
unpivot
(
  val
  for col in (C1, C2, C3, C4)
) piv
group by id

See SQL Fiddle with Demo. Both versions gives a result:

| id | row_max |
|----|---------|
|  1 |       4 |
|  2 |      11 |
|  3 |       4 |
|  4 |       2 |
|  5 |       8 |

回答2:

You can use the following query:

SELECT id, (SELECT MAX(c) 
            FROM (
                SELECT c = C1
                UNION ALL
                SELECT c = C2
                UNION ALL
                SELECT c = C3
                UNION ALL
                SELECT c = C4
            ) as x(c)) maxC
FROM mytable

SQL Fiddle Demo

回答3:

One method uses cross apply:

select t.id, m.maxval
from table t cross apply
     (select max(val) as maxval
      from (values (c1), (c2), (c3), (c4)) v(val)
     ) m