SQL Server partition unnormalized data

Question

I think there might be a way to partition this to find what I want, but I can't see it. I have some slightly unnormalized data about product sets:

SetItemID   ComponentItemID quantity    IsPrimary
123         234             1           1
123         345             2           0
456         567             3           1
456         678             2           0

I need to find sets where the quantity's described are, for example, IsPrimary component's quantity > 'IsPrimary=0component'squantity. I also need to find sets where quantities are equal between components, etc. It's ok to use multiple statements for this, if necessary

All I have so far is a partial PARTITION statement that may or may not be the right way to do this, but I can't figure out how to compare quantities within sets:

WITH setdata as
(select *, row_number() OVER(
    PARTITION by s.setitemid order by s.setitemid) position from set_table s )
// descending just to get newer sets
SELECT* from setdata order by setitemid desc

score 0 · Accepted Answer · answered Sep 07 '12 at 13:35

If I'm following your requirements right, this should do it:

SELECT p1.SetItemId
 from (--  IsPrimary = 1
       select SetItemID, ComponentItemId, Quantity
        from SetData
        where IsPrimary = 1) p1
  inner join (--  IsPrimary = 0
              select SetItemID, ComponentItemId, Quantity
               from SetData
               where IsPrimary = 0) p0
   on p0.SetItemID = p1.SetItemID
    and p1.Quantity > p0.Quantity
  --and p1.Quantity = p0.Quantity

Use that last line for sets with equal quantities.

markA · Answer 2 · 2012-09-07T13:56:46.447

SELECT *
FROM   set_table a
WHERE  isPrimary = 1
       AND quantity > (SELECT quantity
                       FROM   set_table b
                       WHERE  a.setItemId = b.setItemId
                              AND isPrimary = 0)
ORDER  BY setItemId DESC

Or

SELECT a.*
FROM   set_table a
       INNER JOIN set_table b
               ON a.setItemId = b.setItemId
                  AND a.isPrimary = 1
                  AND b.isPrimary = 0
WHERE  a.quantity > b.quantity

SQL Server partition unnormalized data

2 Answers2