Как получить максимум из нескольких колонок одной таблицы?
01.if object_id ( N'dbo.test_table', N'U' )
is not null
02.  drop table dbo.test_table;
03.go
04. 
05.create table dbo.test_table ( id int identity
06.                            , a int
07.                            , b int
08.                            , c int
09.                            , d int
10.                            , e int
11.                            );
12.go
13. 
14.insert into dbo.test_table
15.select 10, 5, 12, 56, 2
16.union all
17.select 100, 45, 78, 6, 122
18.union all
19.select 150, 50, 127, 596, 22
20.union all
21.select 3410, 55, 0, -45, 90
22.union all
23.select -10, -5, 0, -56, -2;20.union all
21.select 3410, 55, 0, -45, 90
22.union 24.go

max 01.png

А теперь несколько примеров, как найти максимум в каждой строке по всем колонкам.

1. Выражение CASE (любая версия)
01.select *
02.     , max_val = case when ( a >= b ) and ( a >= c ) and ( a >= d ) and ( a >= e )
03.                      then a
04.                      when ( b >= a ) and ( b >= c ) and ( b >= d ) and ( b >= e )
05.                      then b
06.                      when ( c >= a ) and ( c >= b ) and ( c >= d ) and ( c >= e )
07.                      then c
08.                      when ( d >= a ) and ( d >= b ) and ( d >= c ) and ( d >= e )
09.                      then d
10.                      else e
11.                 end
12.  from dbo.test_table

2. Подзапрос и оператор UNION (любая версия)
01.select *
02.     , max_val = ( select max(i)
03.                     from ( select a
04.                            union all
05.                            select b
06.                            union all
07.                            select c
08.                            union all
09.                            select d
10.                            union all
11.                            select e
12.                          ) t(i)
13.                 )
14.  from dbo.test_table

3. Подзапрос и предложение VALUES (SQL Server 2008 и выше)
01.select *
02.     , max_val = ( select max(i)
03.                     from ( values (a)
04.                                  ,(b)
05.                                  ,(c)
06.                                  ,(d)
07.                                  ,(e)
08.                &nbcode>                                  ,(d)
07.                                  ,(e)
) t(i)
09.                 )
10.  from dbo.test_table

4. Оператор UNPIVOT (SQL Server 2005 и выше)
1.select id, max(val) as max_val
2.  from ( select *
3.          from dbo.test_table t
4.            unpivot ( val for col in ( [a],[b],[c],[d],[e]) ) up
5.        
6.       ) t
7.  group by id

5. Оператор UNPIVOT и ранжирующие функции (SQL Server 2005 и выше)
1.select top 1 with ties id, val
2.  from dbo.test_table t
3.    unpivot ( val for col in ( [a],[b],[c],[d],[e]) ) up
4.  order by row_number() over ( partition by id order by val desc )

6. Оператор CROSS APPLY (SQL Server 2005 и выше)
01.select *
02.  from dbo.test_table t
03.    cross apply ( select top 1 val
04.                      from ( values (a),(b),(c),(d),(e) ) t (val)
05.                    order by val desc
06.                ) ca (max_val)
07. 
08.--Ещё вариант
09. 
desc
06.                ) ca (max_val)
07. 
08.--Ещё вариант
10.select id, a, b, c, d, e, max_val
11.  from dbo.test_table t
12.    cross apply ( select row_number() over (order by val desc) i, val
13.                      from ( values (a),(b),(c),(d),(e) ) t (val)
14.                ) ca (i, max_val)
15.  where ca.i = 1

7. Inline-функция и CTE (SQL Server 2005 и выше)
01.if object_id ( N'dbo.f_get_max', N'IF' ) is not null
02.  drop function dbo.f_get_max;
03.go
04.create function dbo.f_get_max (
05.    @a int
06.  , @b int
07.  , @c int
08.  , @d int
09.  , @e int
10.) returns table
11.as
12.return
13.with cte
14.as
11.as
12.return
13.with cte
14.as
15.( select val from
16.    ( values (@a),(@b),(@c),(@d),(@e)
17.    ) t(val)
18.)
19.select max(val) as max_val from cte;
20.go
21. 
22.select * from dbo.test_table t
23.  cross apply dbo.f_get_max ( a, b, c, d, e )
24.go


А теперь посмотрим на производительность этих запросов, чтобы определить, какой из вариантов более предпочтителен. Тест буду проводить также на 5 колонках таблицы с 10 млн. записей.
001.set nocount on;
002.go
003. 
004.if object_id ( N'dbo.test_table', N'U' ) is not null
005.  drop table dbo.test_table;
006.go
007. 
008.create table dbo.test_table ( id int identity primary key clustered
009.                            , a int
010.                            , b int
011.                            , c int
012.                            , d int
013.                            , e int
014.                            );
015.go
016. 
, e int
014.                            );
015.go
017.--Генерим 10 млн строк
018.with cte
019.as
020.(
021.  select 1 i
022.  union all
023.  select i+1 from cte where i < 10000000
024.)
025.insert into dbo.test_table
026.select checksum ( newid() ) % 1000 as a
027.     , checksum ( newid() ) % 1000 as b
028.     , checksum ( newid() ) % 1000 as c
029.     , checksum ( newid() ) % 1000 as d
030.     , checksum ( newid() ) % 1000 as e
031.  from cte
032.option ( maxrecursion 0 );
033. 
034.declare @val int;
035. 
036.print '1. Выражение CASE (любая версия)';
037.set statistics time on;
038. 
039.select @val =  case when ( a >= b ) and ( a >= c ) and ( a >= d ) and ( a >= e )
040.                    then a
041.                    when ( b >= a ) and ( b >= c ) and ( b >= d ) and ( b >= e )
042.                    then b
043.                    when ( c >= a ) and ( c >= b ) and ( c >= d ) and ( c >= e )
044.                    then c
045.                    when ( d >= a ) and ( d >= b ) and ( d >= c ) and ( d >= e )
046.                    then d
047.                    else e
048.                 end
049.  from dbo.test_table;
050. 
051.set statistics time off;
052. 
053.print '2. Подзапрос и оператор UNION (любая версия)';
054.set statistics time on;
055. 
056.select @val = ( select max(i)
057.                     from ( select a
058.                            union all
059.                            select b
060.                            union all
061.                            select c
062.                            union all
063.                            select d
064.                            union all
065.                            select e
066.                          ) t(i)
067.                 )
068.  from dbo.test_table;
069. 
070.set statistics time off;
071. 
072.print '3. Подзапрос и предложение VALUES (SQL Server 2008 и выше)';
073.set statistics time on;
074. 
075.select @val = ( select max(i)
076.                  from ( values (a)
077.                              ,(b)
078.                              ,(c)
079.                              ,(d)
080.                              ,(e)
081.                      ) t(i)
082.              )
083.  from dbo.test_table;
084. 
085.set statistics time off;
086. 
087.print '4. Оператор UNPIVOT (SQL Server 2005 и выше)';
088.set statistics time on;
089. 
090.select @val = max(val)
091.  from ( select *
092.          from dbo.test_table t
093.            unpivot ( val for col in ( [a],[b],[c],[d],[e]) ) up
094.        
095.       ) t
096.  group by id;
097. 
098.set statistics time off;
099. 
100.print '5. Оператор UNPIVOT и ранжирующие функции (SQL Server 2005 и выше)';
101.set statistics time on;
102. 
103.select top 1 with ties @val = val
104.  from dbo.test_table t
105.    unpivot ( val for col in ( [a],[b],[c],[d],[e]) ) up
top 1 with ties @val = val
104. &nbsround-color:#f8f8f8;">106.  order by row_number() over ( partition by id order by val desc );
107. 
108.set statistics time off;
109. 
110.print '6. Оператор CROSS APPLY (SQL Server 2005 и выше)';
111. 
112.print 'CROSS APPLY 1';
113.set statistics time on;
114.select @val = max_val
115.  from dbo.test_table t
116.    cross apply ( select top 1 val
117.                      from ( values (a),(b),(c),(d),(e) ) t (val)
118.                    order by val desc
119.                ) ca (max_val);
120. 
121.print 'CROSS APPLY 2';
122.select @val = max_val
123.  from dbo.test_table t
124.    cross apply ( select row_number() over (122.order by val desc) i, val
125.                      from ( values (a),(b),(c),(d),(e) ) t (val)
126.                ) ca (i, max_val)
127.  where ca.i = 1;
128. 
129.set statistics time off;
130. 
131.print '7. Inline-функция и CTE (SQL Server 2005 и выше)';
132.set statistics time on;
133. 
134.select @val = max_val from dbo.test_table t 
135.  cross apply dbo.f_get_max ( a, b, c, d, e );
136. 
137.set statistics time off;
138.go

Результат ниже:
01.1. Выражение CASE (любая версия)
02. 
03.SQL Server Execution Times:
04.   CPU time = 3416 ms,  elapsed time = 3416 ms.
05.----------------------------------------------------------------------
06. 
07.2. Подзапрос и оператор UNION (любая версия)
08. 
09.SQL Server Execution Times:
10.   CPU time = 12777 ms,  elapsed time = 3848 ms.
11.----------------------------------------------------------------------
12. 
13.3. Подзапрос и предложение VALUES (SQL Server 2008 и выше)
14. 
15.SQL Server Execution Times:
16.   CPU time = 13182 ms,  elapsed 3. Подзапрос и предложение VALUES (SQL Server 2008 и выше)
14. 
15.time = 3968 ms.
17.----------------------------------------------------------------------
18. 
19.4. Оператор UNPIVOT (SQL Server 2005 и выше)
20. 
21.SQL Server Execution Times:
22.   CPU time = 22246 ms,  elapsed time = 15966 ms.
23.----------------------------------------------------------------------
24. 
25.5. Оператор UNPIVOT и ранжирующие функции (SQL Server 2005 и выше)
26. 
27.SQL Server Execution Times:
28.   CPU time = 153957 ms,  elapsed time = 239071 ms.
29.----------------------------------------------------------------------
30. 
31.6. Оператор CROSS APPLY (SQL Server 2005 и выше)
32. 
33.CROSS APPLY 1
34. 
35.SQL Server Execution Times:
36.   CPU time = 22963 ms,  elapsed time = 6273 ms.
37. 
38.CROSS APPLY 2
39. 
40.SQL Server Execution Times:
41.   CPU time = 33244 ms,  elapsed time38.CROSS APPLY 2
= 9270 ms.
42.----------------------------------------------------------------------
43. 
44.7. Inline-функция и CTE (SQL Server 2005 и выше)
45. 
46.SQL Server Execution Times:
47.   CPU time = 12246 ms,  elapsed time = 3787 ms.