How to see what rows are missing between two select statements in SQLite?-CodePudding

I have a single table view that has a group column and a data column (among other columns). In a particular group, there should be n rows of the same set of text in the same order. However, I'm finding that in some groups, some rows are missing. I'd like to query the view so that I can see what rows are missing.

Concrete example:

 -------- ------- 
| Group  | Data  |
 -------- ------- 
| 1      | row 1 |
| 1      | row 2 |
| 1      | row 3 |
| 2      | row 1 |
| 2      | row 3 |
 -------- -------

Group 2 has "row 2" missing, and I'd like that output. Something like:

 ------- 
| Data  |
 ------- 
| row 2 |
 -------

Is this possible?

CodePudding user response：

You need to take the COUNT of Data column and then find count(Data) is less than Unique number of Group.

You can achieve it using below.

Select
Data,Count(*)
from tab
Group By Data
having Count(*)<(select count(Distinct Grp) from tab);

DB Fiddle: Try it here