HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Select rows, where 3 columns have the same values

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
rowssamethecolumnswherevaluesselecthave

Problem

I have a structure like this:

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    9


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:

col0 col1 col2 col3
 1    1    1    1
 1    1    1    2
 1    1    1    3
 2    3    4    5
 2    3    4    9


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