PostgreSQL – Select Rows with Different ID but Same Values

postgresql

I would like to find all records with the same values but different id's.

Here is the data I currently have:

+------+------+
|id    |userid|
+------+------+
|1     | 5    |
+------+------+
|1     | 8    |
+------+------+
|2     | 10   |
+------+------+
|2     | 12   |
+------+------+
|3     | 5    |
+------+------+
|3     | 8    |
+------+------+
|4     | 8    |
+------+------+
|4     | 13   |
+------+------+

id 1 has the same exact userid values as id 3. I want to see all the tables that have the same userid values.

Here is the results I would like it to return:

+------+------+
|id    |userid|
+------+------+
|1     | 5    |
+------+------+
|1     | 8    |
+------+------+
|3     | 5    |
+------+------+
|3     | 8    |
+------+------+

Im using Postgres 9.6.

Best Answer

One possible way is to use aggregation and array_agg() to get an array of the user IDs per ID. You can then inner join back to the original table and use EXISTS to filter for different IDs that share the same array, i.e. user ID. For convenience the derived table containing the aggregation can be put in a CTE.

WITH cte
AS
(
SELECT t.id,
       array_agg(t.userid ORDER BY t.userid) userids
       FROM elbat t
       GROUP BY id
)
SELECT t1.*
       FROM elbat t1
            INNER JOIN cte c1
                       ON c1.id = t1.id
       WHERE EXISTS (SELECT *
                            FROM cte c2
                            WHERE c2.id <> c1.id
                                  AND c2.userids = c1.userids);

db<>fiddle

Related Question