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

Postgresql – Postgres Select Code with Multiple Values

I am trying to write a query that tells me every distinct code in my table that has more than 1 distinct display associated with it. Example:

code   display
1       North
2       East
2       South
2       South
3       East
4       West
4       North

In that example I would be looking to return 2 and 4 since those two are the only codes with more than 1 distinct display.

What I have so far is below, but I am honestly not sure if I am even in the right direction. Some guidance would be much appreciated!

select code, display
from directions
group by code where count(distinct display) > 1
Tags:

2

Answers


  1. 0

    Use HAVING COUNT(DISTINCT display) > 1:

    SELECT code
FROM directions
GROUP BY code
HAVING COUNT(DISTINCT display) > 1;

    Online demo: https://onecompiler.com/mysql/42t5dxy6v

    Login or Signup to reply.
  2. 0

    select distinct code, display from directories gives table of distinct pairs. Now:
    select code, count(*) from (select distinct code, display from directories) as a group by code having count(*)>1 is probably the answer.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search