2

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 Server Compact Edition.

Ryan
  • 305
  • 2
  • 5
  • 18

3 Answers3

2

I was able to come up with the solution so I thought I would share it:

SELECT x.RecordID, VendorSource, VendorPrice
FROM ClientData x
INNER JOIN Sources s ON x.RecordID = s.RecordID
INNER JOIN Prices p ON s.SourceID = p.SourceID
INNER JOIN (SELECT c.RecordID, MIN(Weight) min_weight
            FROM ClientData c
            INNER JOIN Sources s ON c.RecordID = s.RecordID
            INNER JOIN Prices p ON s.SourceID = p.SourceID
            WHERE Weight != 0 
            GROUP BY c.RecordID) w ON x.RecordID = w.RecordID
WHERE p.Weight = w.min_weight

This allows the minimum weight to be populated on a RecordID level in the derived table, so there is 1 weight per RecordID.

For all those who gave answers, thank you; I appreciate the help and any guidance that was offered.

Ryan
  • 305
  • 2
  • 5
  • 18
0

I think you need to change your structure up a little bit to actually make this work as you would like it to. Basically the way you have it a price record is set up against a Source rather than against the Item which seems to be in the ClientData table. By removing the c.Record number column from the Sources table and putting it into the Prices table you should get the correct One(ClientData) to many (Prices), and One(ClientData) to many(Sources) relationships that I think you need.

select c.RecordID, c.Description, s.Source, p.Price, p.Type, p.Weight
from ClientData c
inner join Prices p ON c.RecordID = p.RecordID
inner join Sources s ON s.SourceID = p.SourceID
   AND p.Weight> 0
LEFT OUTER JOIN #Prices p2 ON c.RecordID = p2.RecordID
   AND p2.PriceID <> p.priceID
   AND p2.Weight > 0
   AND p2.Weight < p.Weight
WHERE p2.SourceID IS NULL

If you make the change specified above then this query will returns the exact data that you are looking for.

collusionbdbh
  • 701
  • 1
  • 6
  • 18
  • This doesn't appear to be returning the correct result. If I understand the criteria in the last LEFT OUTER JOIN, if there are Weights 0,1,2,3 for a RecordID, having Weight > 0 and < p.Weight would only rule out the 0 and 3 weight, leaving records with 1 and 2. If 0,1,2,3 are present for a record, I want to only return prices that have a weight of 1. – Ryan Jun 06 '12 at 17:46
  • No, it would rule out the 2 as well, it basically says where p2's weight is greater than 0 but less that p's weight. So in your above scenario if the weight of p = 1, then the weight 2 is greater than zero but not less than the weight of p, same with 3 and four. Only rows with a weight of one will be shown. – collusionbdbh Jun 07 '12 at 21:44
  • I added DISTINCT to the above to get the correct result set. I have tested this and it works perfectly for the setup I was using. The thing that might be an issue for you is the conditions on the left joins if your setup is a little different. If 1, 2, and 3 join to the same clientData row then only 1 will be shown. However if 1 and 3 join to the same ClientData row and two joins to another ClientData row then 1 and 2 will be shown. – collusionbdbh Jun 07 '12 at 21:55
  • Actually, I think it is correc, could you post the results you get for that query if it is not right? – collusionbdbh Jun 07 '12 at 22:29
  • Sorry it has taken me a while because I have been a bit busy but I have changed my answer above. hope it helps. – collusionbdbh Jun 10 '12 at 22:00
  • Thanks for the suggestion about DB structure, but unfortunately I'm just the business analyst, not a DBA or even a developer so the DB structure is already set in stone. Your answer will still return 2 weights if I have prices with weights of 1,2,3. If I have those 3 weights for the same RecordID, the min weight is 1, so only prices with 1 should be showing up. – Ryan Jun 13 '12 at 00:34
0

You can use RANK() with a Partition over RecordId with increasing weights to 'rate' each row (after excluding zero weights entirely), and then simply filter out the top ranked rows. The CTE used just to keep the second query simple + clear

;WITH MyRecords AS
(
    -- Your source query goes here
    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
)
SELECT RecordID, [Description], [Source], [Price], [Type], [Weight]
FROM
(
    SELECT RecordID, [Description], [Source], [Price], [Type], [Weight], 
           -- With ranking, the lower the weight the better
           Rnk = RANK() OVER (PARTITION BY RecordId ORDER BY [Weight] ASC)
    FROM MyRecords
    -- But exclude Weight 0 entirely
    WHERE [Weight] > 0
) RankedRecords
-- We just want the top ranked records, with ties
WHERE Rnk = 1

Edit CE constraint added after the post. See How would I duplicate the Rank function in a Sql Server Compact Edition SELECT statement? on how to simulate RANK() over in CE.

Community
  • 1
  • 1
StuartLC
  • 104,537
  • 17
  • 209
  • 285