PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to write SQL query to filter for ids where its other column values are the same?

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

What I am trying to do is to get all of the ids where the value of 995 and 996 are the same.

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

I hope the above makes sense, and any help is much appreciated

Tags:

2

Answers


  1. 0

    Something like this:

    SELECT qr2.id FROM
(SELECT id, val, count(1)
FROM table_1
GROUP BY id, val
HAVING count(1) > 1) qr1
JOIN
(SELECT id, fid
FROM table_1) qr2
ON qr1.id = qr2.id
GROUP BY qr2.id
ORDER BY qr2.id;
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search