Find minimum number in one record 1

[jadams0173]

Using SQL 2000

I've got about 4000 records that have 4 columns with numbers in them. I need to take the minimum number from the 4 columns and place it in another column. Is there a way to do it without selecting each value and gonig through the if this one is less than that one.....

The lowest column in the example is the one I need to update.

Oh yeah. cola - cold must be greater than 0.

example data
col a colb colc cold lowest
45 93 92 8 8
23 0 45 65 23

 

[gmmastros]

Do you have a primary key/unique identifier column for this table?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

Hey George!

Yes the column is PK_ID. It's just and IDENTITY column.

 

[markros]

;with cte as (select Pk_ID, Cola as Col
union all
select Pk_Id, colb as Col
etc)

select Pk_ID, min(Col) as MinValue from cte group by Pk_ID

PluralSight Learning Library

 

[gmmastros]

Similar concept as Markros, but this one will actually work with SQL2000. Markros's version would work with SQL2005 and up.

Declare @Temp Table(PK_ID Int, ColA int, ColB Int, ColC int, ColD int, Lowest Int)

Insert Into @Temp Values(1,45,93,92,8, 8)
Insert Into @Temp Values(2,23,0 ,45,65,23)

Update Temp
Set    Temp.Lowest = A.MinValue
From   @Temp Temp
       Inner Join (
         Select PK_ID, Min(ColA) As MinValue
         From   (
                Select PK_ID, ColA
                From   @Temp
         
                Union All  
         
                Select PK_ID, ColB
                From   @Temp
         
                Union All  
         
                Select PK_ID, ColC
                From   @Temp
         
                Union All  
         
                Select PK_ID, ColD
                From   @Temp
                ) As A
         Where  ColA > 0
         Group By PK_ID
         ) As A
         On Temp.PK_ID = A.PK_ID

Select * From @Temp

Of course, I used an @Temp table to demonstrate. You'll need to tweak this for your table.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Er... Uh.... looks like I hardcoded the results too. In the code above, where I hardcode the data for the table, you should replace the last 2 columns with NULL so that you can see that the data actually does update like you want it to.

Declare @Temp Table(PK_ID Int, ColA int, ColB Int, ColC int, ColD int, Lowest Int)

Insert Into @Temp Values(1,45,93,92,8, [!]null[/!])
Insert Into @Temp Values(2,23,0 ,45,65,[!]null[/!])

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

George,

As usual the solution worked perfectly. Thanks again!