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.