How to filter inconsistent records in sqlite

Question

Say I have SQLite table with the following records:

recID	productID	productName
1	1	Product A
2	2	Product B
3	2	Product C
4	3	Product D
5	3	Product D

recID = primary key, auto increment.

If I run:

SELECT productID, productName 
FROM table 
GROUP BY productID, productName

Result is:

productID	productName
1	Product A
2	Product B
2	Product C
3	Product D

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:

productID	productName
2	Product B
2	Product C

forpas · Accepted Answer · 2022-06-03 13:53:15Z

3

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
);

edited Jun 3, 2022 at 13:53

answered Jun 3, 2022 at 13:47

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Albert Tobing Over a year ago

Thank you. It worked but super slow. It took 11.262 s in a table with just 15000 rows using nvme ssd gen 4 which is supposedly to be really fast. I think loading with SELECT * and filtering in app is much faster. My goal is to have faster result than processing via app

forpas Over a year ago

@AlbertTobing a condition like t2.productID = t1.productID AND t2.productName <> t1.productName needs a composite index for (productID, productName) in that order.

Albert Tobing Over a year ago

Your second suggestion performed much much faster ! 0.004s. This is more like it although I am not sure if this is good enough for table with 15000 rows

forpas Over a year ago

@AlbertTobing probably the subquery returns a small dataset. This is the only case that IN would be preferred over EXISTS.

Albert Tobing Over a year ago

A little bit update. The query doesn't seem to work when I introduce more inconsistency. For example if I add 4 more records to table above: [ Product ID 2 --> Product E ]. [ Product ID 2 --> Product E ]. [ Product ID 2 --> Product F ]. [ Product ID 2 --> Product F ]

|

Collectives™ on Stack Overflow

How to filter inconsistent records in sqlite

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related