cursors with where clause

[sbcaco]

I am trying to loop thru a cursor with 100 rows in it and get one record from a 2nd table that matches on a field called "Name". they are unique names in the 100 row cursor.

I want the record from the 2nd table to be based on 3 fields' order:

name costa costb costc
Apple 999 999 3.00
Apple 1.01 1.33 1.50
Apple 0.78 0.77 1.60
Apple 2.50 999 1.50

in this case I would want the 3rd one -- the sort order being costa, costb, costc desc.

how can I do this in one select statement instead of using a cursor? I am using sql 2000.

thanks.

[iburyak]

Did you see this solution?
http://www.thescripts.com/forum/thread627490.html

Did you try it?

[sbcaco]

sorry, I didn't see your answer before I posted this question.

I tried it last night and it didn't work. here is an example of what I tried:

SELECT a.productname, b.type, b.id,
b.description, b.costa, b.costb, b.costc
from [table a] as [a]
JOIn [table b] as [b]
on a.productname = b.productname
WHERE b.costa = (SELECT top 1 costa
FROM [table b]
WHERE productname = a.productname ORDER BY costa, costb, costc desc )
and b.costb = (SELECT top 1 costb
FROM [table b]
WHERE productname = a.productname ORDER BY costa, costb, costc desc )
and b.costc = (SELECT top 1 costc
FROM [table b]
WHERE productname = a.productname ORDER BY costa, costb, costc desc )

-------------------------
I got this error message:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'b.type'.

thanks!

Did you see this solution?
http://www.thescripts.com/forum/thread627490.html

Did you try it?

[iburyak]

In my original query I don't have following columns that were added by you .
Make sure that all columns you add exist in your tables.


[PHP]
b.type, b.id, b.description, [/PHP]



Good Luck.