Minimum Costs... 1

[jtstruck]

I have a table that has about 500,000 records, each record has four different cost fields. I would like to populate a fifth cost field with the lowest of the four costs in the other fields. Is there an easy/quick way to do this? I wrote a Cursor that does it, but it took over a half hour to run, which is unacceptable. Thanks.

 

[DotNetGnat]

Something like this:

SELECT A.Product, A.Cost
into #temp
FROM mytable A WHERE Cost IN (
SELECT Top 1 Cost From mytable B
WHERE A.Product=B.Product
Order by Cost Asc
)

now you have the #temp table with all the records with minimum cost...then you can do the update....

-DNG

 

[vongrunt]

There are four fields (cost1-cost4) and you want to find the lowest one?

Try this:

update myTable set cost5 = coalesce(cost1, cost2, cost3, cost4)
update myTable set cost5 = cost2 where cost2 < cost5
update myTable set cost5 = cost3 where cost3 < cost5
update myTable set cost5 = cost4 where cost4 < cost5

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)