Search code examples
sqlpostgresqlaggregate-functions

How to filter for IDs where another column value is the same for all rows?

I have the following table in PostgreSQL:

id        fid        val 
--------------------------
200       995        XXLL
200       996        XXLL
201       995        OOOP
201       996        OOOS
202       995        OKIL
202       996        OKIL
203       995        LLLL
203       996        CCCC

I am trying to get all ids with two rows for fid 995 and 996 and val being the same.

So in the above example, I would like the output of the SQL query to be just 200, 202. For 201 and 203 the values for 995 and 996 are different.

Solution

  • Based on your sample data

    SELECT X.ID,X.fid,X.val
FROM your_table AS X
JOIN
(
  SELECT C.ID
  FROM your_table AS C
  GROUP BY C.id
  HAVING MIN(C.VAL)=MAX(C.VAL)
)SQ ON X.id=SQ.id