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]
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.