selecting minium of 3 - 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

SQL Server Development (2000)

selecting minium of 3

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-08-23 : 14:14:43

need to select min( 1, min (a, b))

first compare which is min a or b

then compare 1 and result of (a and b)

select the minimum of the 1, (a or b)

create table #temp (a float, b float)
insert into #temp values (1.9, .7)
insert into #temp values (.5, .7)
insert into #temp values (.8, .7)

this code does not work

select
case when 1 < b and 1 < a then 1 end a,
case when a < b then a else b end b
from #temp

Ashley Rhodes

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-23 : 14:20:30

[code]
create table #temp (a float, b float)
insert into #temp values (1.9, .7)
insert into #temp values (.5, .7)
insert into #temp values (.8, .7)
insert into #temp values (1.8, 1.7)

Select
a, b,
case
when a < 1 then
case when a < b then a else b end
when b < 1 then
b
~~case when b < a then b else a end~~
else 1
end
From #temp

Drop Table #temp
[/code]

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-08-24 : 07:45:07

Or...

Select 
    a, b,
    case when 1 < a and 1 < b then 1
         when a < b then a
         else b
    end
From #temp

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-24 : 13:21:10

quote:
Originally posted by RyanRandall

Or...

Select
a, b,
case when 1 < a and 1 < b then 1
when a < b then a
else b
end
From #temp

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

yeah... thats better!

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources