Wednesday, February 7, 2007

MS SQL server: greatest value

How to get the greatest value from 3 or more different columns?



-- create a test table (just for example)
create table #t (a int, b int, c int)

-- insert sample data
insert #t
select 1, 2, 3
union all select 4, 5, 6
union all select 7, 8, 9

-- select a max value
select max(mx) from (
select mx = max(a)
from #t
union
select max(b)
from #t
union
select max(c)
from #t
) as t

drop table #t

-----------
9

No comments: