MSSQL multi-field Maximum Value Based on Range

--> Title: generate a sequence number.
--> Author: wufeng4552
--> Date: 15:08:41
 
Declare @ t table ([col1] int, [col2] int, [col3] int, [col4] int, [col5] int, [col6] int, [col7] INT)
Insert @ t
Select 1, 10, 20, 30, 40, 50, 60 Union all
Select 2, 60, 30, 45, 20, 52, 85 Union all
Select, 21
-- Method 1
Select [col1],
Max ([col2]) maxcol
From
(Select [col1], [col2] From @ t
Union all
Select [col1], [col3] From @ t
Union all
Select [col1], [col4] From @ t
Union all
Select [col1], [col5] From @ t
Union all
Select [col1], [col6] From @ t
Union all
Select [col1], [col7] From @ t
) T
Where [col2] between 20 and 60 -- condition restrictions
Group by [col1]
/*
Col1 maxcol
----------------------
1 60
2 60
3 56

(3 data columns are affected)

*/
-- Method 2
Select [col1],
(Select max ([col2]) from
(
Select [col2]
Union all select [col3]
Union all select [col4]
Union all select [col5]
Union all select [col6]
Union all select [col7]
) T
Where [col2] between 20 and 60) as maxcol -- specify to query zookeeper
From @ t
/*
(3 data columns are affected)
Col1 maxcol
----------------------
1 60
2 60
3 56
*/