By using SQlite, I'd like to get all rows that show in a specific column only one single distinct value. Like from following table:
A B 1 2 2 1 3 2 4 3 5 1 6 1 7 2 8 4 9 2 Here I'd like to get only row Nr. 4 an 8 as there values (3 and 4) occur only once in the entire column.
You could use a query like this:
SELECT * FROM mytable WHERE B IN (SELECT B FROM mytable GROUP BY B HAVING COUNT(DISTINCT A)=1) Please see fiddle here.
Subquery will return all B values that are present only once (you could also use HAVING COUNT(*)=1 in this case), the outer query will return all rows where B is returned by the subquery.
