better way to write this.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

better way to write this....

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2008-09-16 : 15:34:26

I need to identified the small amount of the 3 column...this is what I did, but I'm pretty sure there are better way to do this.

thanks

declare @tbl1 table (id int identity(0,1), col1 int,col2 int ,col3 int)

insert into @tbl1(col1,col2,col3)
select 8,9,1 union all
select 7,8,9 union all
select 2,4,1 union all
select 2,2,4 union all
select 3,1,5

select id,min(Column1)FN from (
select id, col1 as Column1 from @tbl1 union all
select id, col2 from @tbl1 union all
select id, col3 from @tbl1)tb1
group by id

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-16 : 16:15:58

select id,min(case when col1<col2 and col1<col3 then col1 when col2<col1 and col2<col3 then col2 when col3<col1
and col3<col2 then col3 else col1 end)
from @tbl1
group by id

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-17 : 02:43:16

or

select id,case when col1<col2 and col1<col3 then col1 when col2<col3 then col2 else col3 end
from @tbl1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources