MIN Function in SQL Join Query

[YaBabyYa]

Hello,

I am trying to make a calculation on two tables:

Table # 1
---------
#words (Word varchar, A float, B float, C float,.. Z float)




Table # 2
---------
#input_word

#input_word table has a subset of the columns (A .. Z)


Example:


#words

Word---A---B---C--...E.....S....T....... X Y Z
---------------------------------------------
Test----------------.25---.25--.50
Cat---.33-----.33--------------.33
Bays--.25-.25-------------.25-------------.25



#input_word: aword

A D O R W
------------------
.2 .2 .2 .2 .2





I am trying to join #input_word and #words

such that the MIN value of the two columns is in the output set

For the above example, output would be:


Word A D O R W
------------------------
TEST 0 0 0 0 0
CAT .2 0 0 0 0
BAYS .2 0 0 0 0


The recordset takes the columns of #input_word and then taken the value as the MIN of #input_word or #words

I have implemented this query in a while loop, but it is extremely slow. I am looking for an optimized way to get the above calculation done.

 

[Qik3Coder]

How many times are you running your while loop?
Have you looked at using a cursor instead of a while loop?
How many rows are in your #word table?

Will #input_word ever have more than one row?
If not, then why are you using a table, and not a couple of variables?

-The answer to your problem may not be the answer to your question.

 

[ESquared]

You have things in columns that belong in rows.

Instead of

[tt]#testword
A D O R W
.2 .2 .2 .2 .2[/tt]

it should be

[tt]#testword
Let Amt
A .2
D .2
O .2
R .2
W .2[/tt]

And instead of

[tt]
#words
Word---A---B---C--...E.....S....T....... X Y Z
---------------------------------------------
Test----------------.25---.25--.50
Cat---.33-----.33--------------.33
Bays--.25-.25-------------.25-------------.25[/tt]

it should be

[tt]#words
Word Let Amt
Test E .25
Test S .25
Test T .5
Cat A .33
Cat C .33
Cat T .33
Bays A .25
Bays B .25
Bays S .25
Bays Y .25[/tt]

Now you can do proper queries:

SELECT
   W.Word,
   W.Let,
   Match = CASE WHEN T.Amt < W.Amt THEN T.Amt ELSE W.Amt END
FROM
   #testword T
   INNER join #words W ON T.Let = W.Let

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]