Here's my table:

I'm trying to find occurrences of ItemID ItemName ItemBatch that have a non-unique TrackingNumber. So in the example above, there are 3 occurrences of a bag 1 and at least 1 of those rows has a different TrackingNumber from any of the other rows. In this case 958103 is different from 498239 so it should be a hit.

For b paper 2 the TrackingNumber is unique for all the respective rows so we ignore this. Is there a query that can pull this combination of columns with 3 identical fields and 1 non-unique field?

CodePudding user response：

Yet another option:

SELECT * 
FROM tab
WHERE ItemBatch IN (SELECT ItemBatch 
                    FROM tab 
                    GROUP BY ItemBatch, TrackingNumber
                    HAVING COUNT(TrackingNumber) = 1)

This query finds the combination of (ItemBatch, TrackingNumber) that occur only once, then gets all rows corresponding to their ItemBatch values.

Try it here.

CodePudding user response：

You can use GROUP BY and HAVING

SELECT
  t.ItemID,
  t.ItemName,
  t.ItemBatch,
  COUNT(*)
FROM YourTable t
GROUP BY
  t.ItemID,
  t.ItemName,
  t.ItemBatch
HAVING COUNT(DISTINCT TrackingNumber) > 1;

Or if you want each individual row you can use a window function. You cannot use COUNT(DISTINCT in a window function, but you can simulate it with DENSE_RANK and MAX

SELECT
  t.*
FROM (
    SELECT *,
      Count = MAX(dr) OVER (PARTITION BY t.ItemID, t.ItemName, t.ItemBatch)
    FROM (
        SELECT *,
          dr = DENSE_RANK() OVER (PARTITION BY t.ItemID, t.ItemName, t.ItemBatch ORDER BY t.TrackingNumber)
        FROM YourTable t
    ) t
    
) t
WHERE t.Count > 1;

db<>fiddle