Row-wise maximum in T-SQL

Question

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

take a look at pivot http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — Fabian Bigler, Apr 16 '15 at 21:10

Taryn · Accepted Answer · 2015-04-16T21:19:13.550

8

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 |

edited Apr 16 '15 at 21:19

answered Apr 16 '15 at 21:10

Taryn

242,637
56
362
405

is cross apply neater than pivot? – Fabian Bigler Apr 16 '15 at 21:14
@FabianBigler Pivot converts rows of data into columns, this is the opposite. It's taking the multiple columns and turning it into rows to get the max value. – Taryn Apr 16 '15 at 21:15
I thought you could use unpivot too instead of cross apply – Fabian Bigler Apr 16 '15 at 21:16
@FabianBigler I just included an unpivot version in my answer. – Taryn Apr 16 '15 at 21:17
Yes, thank you. But which one should I use? – Fabian Bigler Apr 16 '15 at 21:19
@FabianBigler You can use either one. – Taryn Apr 16 '15 at 21:19

score 6 · Answer 2 · answered Apr 16 '15 at 21:14

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

score 1 · Answer 3 · answered Apr 16 '15 at 21:15

1

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

answered Apr 16 '15 at 21:15

Gordon Linoff

1,242,037
58
646
786

1

Why the downvote? This and Giorgos's answer are the best so far. – Gordon Linoff Apr 16 '15 at 21:15
2

Not my vote, but why's that? Care to elaborate on why this and Giorgos's are the best so far? – Taryn Apr 16 '15 at 21:22
2

@bluefeet . . . Your answer requires aggregating over the entire data set. The correlated subquery and `cross apply` only require aggregating within a small amount of data. – Gordon Linoff Apr 16 '15 at 21:25
3

@bluefeet's answer has two suggestions actually, the first of them being the same as yours, only using slightly different syntax. Anyway, why do you think aggregation over the entire set once is worse then aggregation over a small set many times? – Andriy M Apr 17 '15 at 06:13
@AndriyM . . . When I wrote the comment, bluefeet didn't have two answers. The main point is that general-purpose aggregation algorithms do not scale O(n) in both memory and processing, so doubling the number of rows should take more than twice the effort. Operating on a single row should have additional performance advantages because the data is localized when aggregated. Because this type of query is a special case, it is possible that there is some sort of optimization specifically for this case that I'm not aware of. – Gordon Linoff Apr 17 '15 at 13:32

Row-wise maximum in T-SQL

3 Answers3

Linked