--> 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
*/