CODEWITHSUNDEEP
sqlpostgresqlselectpostgresql-9.1
smerlin
smerlin

Reputation: 6566

Select rows with multiple distinct column value combinations

I have a table with 3 columns: id, val1 and val2.

For each distinct value of val2 I want to select all rows for which multiple distinct values of val1 exist.

Example:

| id | val1 | val2 |
|------------------|
| 1  | A1   |  a2  |
| 2  | A1   |  a2  |
| 3  | A1   |  b2  |
| 4  | B1   |  b2  |
| 5  | A1   |  c2  |
| 6  | A1   |  c2  |
| 7  | A1   |  c2  |
| 8  | A1   |  d2  |
| 9  | C1   |  d2  |
| 10 | A1   |  d2  |

Desired result:

| id | val1 | val2 |
|------------------|
| 3  | A1   |  b2  |
| 4  | B1   |  b2  |
| 8  | A1   |  d2  |
| 9  | C1   |  d2  |
| 10 | A1   |  d2  |

I did not manage to come up with any query which allows me to do this, maybe someone else has an idea on how to solve this.

Upvotes: 2

Views: 757

Answers (4)

Er. Anurag Jain
Er. Anurag Jain

Reputation: 1793

Try this query:

SELECT * from tbl where val2 in (SELECT  val2 FROM `tbl`  group by val2 having  
 count(distinct(val1)) > 1)

Upvotes: 2

Andriy M
Andriy M

Reputation: 77707

Using aggregate window functions, you could also do it like this:

SELECT
  id,
  val1,
  val2
FROM (
  SELECT
    *,
    MIN(val1) OVER (PARTITION BY val2) AS minval1,
    MAX(val1) OVER (PARTITION BY val2) AS maxval1
  FROM atable
) s
WHERE minval1 <> maxval1

Upvotes: 2

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20775

SELECT * 
FROM   TABLE_2 
WHERE  EXISTS (SELECT 1 
               FROM   (SELECT val2, 
                              val1, 
                              Count(*) AS Number 
                       FROM   TABLE_2 
                       GROUP  BY val2, 
                                 val1 
                       HAVING Count(*) = 1) a 
               WHERE  TABLE_2.val2 = a.val2) 
ORDER  BY ID

Upvotes: 2

Andomar
Andomar

Reputation: 238176

You could use a having clause to search for val2 with more than one distinct value of val1. For example:

select  yt.*
from    YourTable yt
join    (
        select  val2
        from    YourTable
        group by
                val2
        having  count(distinct val1) > 1
        ) as filter
on      yt.val2 = filter.val2

Upvotes: 6

Related Questions