Say I have sqlite table with the following records:

recID = primary key, auto increment.

If I run: SELECT productID, productName FROM table GROUP BY productID, productName

Result is:

As you can see, productID 2 has inconsistent productName: Product B and Product C. How do I run query just to detect the inconsistent ones? Eg I want the result to be:

CodePudding user response：

Use EXISTS to get a productID with more than 1 productNames:

SELECT t1.productID, t1.productName 
FROM tablename t1
WHERE EXISTS (
  SELECT *
  FROM tablename t2
  WHERE t2.productID = t1.productID AND t2.productName <> t1.productName 
);

Or, for a small dataset use aggregation in a subquery which counts the distinct number of productNames of each productID, with the operator IN:

SELECT productID, productName 
FROM tablename
WHERE productID IN (
  SELECT productID
  FROM tablename
  GROUP BY productID
  HAVING COUNT(DISTINCT productName) > 1
);

CodePudding user response：

We can try to use COUNT window function to compare grouping productID, productName is one and grouping productID higher than one.

Query #1

SELECT productID, productName 
FROM (
    SELECT *,
           COUNT(*) OVER(PARTITION BY productID, productName) cnt1,
           COUNT(*) OVER(PARTITION BY productID) cnt2
    FROM T 
) t1
WHERE cnt1 = 1 AND cnt2 > 1;

View on DB Fiddle