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 |
SQL:
CREATE TABLE t1 (
file INT,
checksum VARCHAR(512)
);
INSERT INTO t1 (file, checksum) VALUES
('2', '1'),
('3', '1'),
('4', '2'),
('4', '2'),
('5', '3'),
('5', '3'),
('6', '3');
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
You need to sign in to view this answers
Leave feedback about this