Select rows, where 3 columns have the same values

Question

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.

• ypercubeᵀᴹ: Why are the two rows with 2 3 4 5 and 2 3 4 9 in the results? 2 is not equal to 3.
• Erwin Brandstetter: Your demo result makes it look like you want to find rows that match another row on the first three columns. And your description matches neither your result nor the accepted answer. Please clarify by editing the question.

Answer 1

SELECT
 col0
 ,col1
 ,col2
 ,col3
FROM
 foo
WHERE
 col0 = col1
 and col0 = col2;

• dArignac: Well it does not do it, unfortunately.
• ypercubeᵀᴹ: @MikeFal the OP has edited the question so he needs something else, not the first description (which you had answered.)

Answer 2

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;

Answer 3

We have to find all touples (col0, col1, col3) that occour at least twice and then get all rows that match the tuples.

Solution A: col0, col1 and col2 do not contain any NULLs

SELECT * FROM 
foo 
WHERE (col0, col1, col2) IN (
 SELECT col0, col1, col2 
 FROM foo 
 GROUP BY 1,2,3 
 HAVING count(*)>1
 );

Solution B: NULLs are allowed:

SELECT f.* 
FROM foo f 
JOIN (
 SELECT col0, col1, col2
 FROM foo
 GROUP BY 1,2,3
 HAVING count(*)>1
 ) q 
ON (
 f.col0 IS NOT DISTINCT FROM q.col0
 AND f.col1 IS NOT DISTINCT FROM q.col1
 AND f.col2 IS NOT DISTINCT FROM q.col2
 );

Answer 4

I find common table expressions (CTEs) to be tremendously helpful in breaking a query like this down into blocks that are easier for me wrap my brain around.

Here is your problem using a CTE:

WITH cte AS (
 SELECT DISTINCT col0, col1, col2
 FROM foo)
SELECT foo.*
FROM foo
 INNER JOIN cte
 ON cte.col0 = foo.col0
 AND cte.col1 = foo.col1
 AND cte.col2 = foo.col2

Here's the SQL Fiddle for it:
http://sqlfiddle.com/#!12/c4c96/2/0

• Paul White: There have to be two or more rows with the same values. As it stands, your query will return every row (since a row will always match itself). See the questioner's own answer.
• Victor: update the fiddle please, it does not works anymore