sqlsqlite

SQL, select by having duplicates in one column, but exclude if also 1:1 duplicate in other column


I need to select values from one column, that have duplicates, but exclude values, that also have duplicates in other column. Working with SQLite. Need query to be reasonably optimal.

Example table:

file checksum
2 1
3 1
4 2
4 2
5 3
5 3
6 3
7 4
7 4
8 4
8 4
9 5
10 6

SQL:

CREATE TABLE t1 (
    file    INT,
    checksum    INT
);

INSERT INTO t1 (file, checksum) VALUES
    ('2', '1'),
    ('3', '1'),
    ('4', '2'),
    ('4', '2'),
    ('5', '3'),
    ('5', '3'),
    ('6', '3'),
    ('7', '4'),
    ('7', '4'),
    ('8', '4'),
    ('8', '4'),
    ('9', '5'),
    ('10', '6');

I end up with query:

select checksum from t1
         where file not in (select file from t1 group by file having count(file) > 1)
         group by checksum
         having count(checksum) > 1

But it dose not select checksum=3,4

Expected result is checksum = [1,3,4]

sqlfiddle


Solution

  • You are filtering by file before you check for checksum totals. It would be better to check for how many distinct files you have rather than filter them.

    select checksum from t1
            group by checksum
            having count(DISTINCT file)>1;
    

    I was able to get your desired result using sqlfiddle.