patternsqlMinor
Select rows, where 3 columns have the same values
Viewed 0 times
rowssamethecolumnswherevaluesselecthave
Problem
I have a structure like this:
I now want to select all rows, that match other rows on the first three columns, so the result of this query using the sample data would be:
How can I achieve this, I'm kinda stuck at the early beginning...
Thanks in advance!
Alex
Update: Clearified the question. Thanks Erwin.
col0 col1 col2 col3
1 1 1 1
1 1 1 2
1 1 1 3
1 2 1 1
1 2 3 1
2 3 4 5
2 3 4 9I now want to select all rows, that match other rows on the first three columns, so the result of this query using the sample data would be:
col0 col1 col2 col3
1 1 1 1
1 1 1 2
1 1 1 3
2 3 4 5
2 3 4 9How can I achieve this, I'm kinda stuck at the early beginning...
Thanks in advance!
Alex
Update: Clearified the question. Thanks Erwin.
Solution
I somehow managed to find a solution, though I do not know if there is a more performant variant:
select
distinct a.col0, a.col1, a.col2, a.col3
from foo a, foo b
where
a.col0 = b.col0 and
a.col1 = b.col1 and
a.col2 = b.col2 and
a.col3 != b.col3
order by
a.col0,
a.col1,
a.col2;Code Snippets
select
distinct a.col0, a.col1, a.col2, a.col3
from foo a, foo b
where
a.col0 = b.col0 and
a.col1 = b.col1 and
a.col2 = b.col2 and
a.col3 != b.col3
order by
a.col0,
a.col1,
a.col2;Context
StackExchange Database Administrators Q#46939, answer score: 3
Revisions (0)
No revisions yet.