I have a set of data, and while the number of fields and tables it joins with is quite complex, I believe I can distill my problem down using the required fields/tables here for illustration regarding this particular problem.
I have three tables: ClientData, Sources, Prices
Here is what my current query looks like before selecting the minimum value:
select c.RecordID, c.Description, s.Source, p.Price, p.Type, p.Weight
from ClientData c
inner join Sources s ON c.RecordID = s.RecordID
inner join Prices p ON s.SourceID = p.SourceID
This produces the following result:
RecordID Description Source Price Type Weight
=============================================================
001002003 ABC Common Stock Vendor 1 104.5 Close 1
001002003 ABC Common Stock Vendor 1 103 Bid 2
001002003 ABC Common Stock Vendor 2 106 Close 1
001002003 ABC Common Stock Vendor 2 100 Unknwn 0
111222333 DEF Preferred Stk Vendor 3 80 Bid 2
111222333 DEF Preferred Stk Vendor 3 82 Mid 3
111222333 DEF Preferred Stk Vendor 2 81 Ask 4
What I am trying to do is display prices that belong to the same record which have the minimum non-zero weight for that record (so the weight must be greater than 0, but it has to be the minimum from amongst the remaining weights). So in the above example, for record 001002003 I would want to show the close prices from Vendor 1 and Vendor 2 because they both have a weight of 1 (the minimum weight for that record). But for 111222333 I would want to show just the bid price from Vendor 3 because its weight of 2 is the minimum, non-zero for that record. The result that I'm after would like like:
RecordID Description Source Price Type Weight
=============================================================
001002003 ABC Common Stock Vendor 1 104.5 Close 1
001002003 ABC Common Stock Vendor 2 106 Close 1
111222333 DEF Preferred Stk Vendor 3 80 Bid 2
Any ideas on how to achieve this?
EDIT: This is for SQL Compact Edition.
