CODEWITHSUNDEEP
sqlpostgresql
Holger
Holger

Reputation: 407

selecting on different column value

Given a table with columns id, selector, name:

  1 |  ONE | A
  2 |  TWO | A
  3 |  ONE | C
  4 |  ONE | C
  5 |  ONE | E
  6 |  ONE | E
  7 |  TWO | E
  8 |  TWO | H

I need a query which returns me all different name values for which there is more than one row and these rows contain at least two different selector values. For the example above I would expect A and E as result. H is not expected because there is only one row. C is not expected because all rows have the same selector. I could use a group by and having clause on the selector column, but then I can't select the name as result...

My try

select selector 
from mytable 
group by selector 
having count(*) > 1

Upvotes: 0

Views: 59

Answers (2)

mucio
mucio

Reputation: 7119

Try this:

   select name
     from Table1
 group by name
   having count(distinct selector) > 1

You can see a demo here

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Use Group by and having clause.

Instead of * in count aggregate use Distinct column_name in count aggregate to filter the group which is having more than one distinct values

select column3 from yourtable 
group by column3 
having count(distinct column2)>1

Upvotes: 2

Related Questions