CODEWITHSUNDEEP
sqlsqlitedistinct
Johannes
Johannes

Reputation: 1064

Get rows with single values using SQlite

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.

Upvotes: 0

Views: 106

Answers (1)

fthiella
fthiella

Reputation: 49049

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.

Upvotes: 1

Related Questions