PostgreSQL: return entries where value in column A shows multiple time for distinct values in column-CodePudding

Imagine the table:

col_A     col_B   
banana      1     
apple       1
banana      45     
banana      1     
kiwi        2
grape       2
grape       33
strawberry  56
strawberry  56

I would like to return:

col_A    col_B
banana    1
banana    45
grape     2
grape     33

I can't think of a way or a function to obtain this result. Hoping for recommendations.

CodePudding user response：

SELECT DISTINCT(col_B), col_A FROM table;

CodePudding user response：

This should work in postgre sql

Schema Definition

CREATE TABLE test_dp (
  "firsttt" VARCHAR(10),
  "secondd" INTEGER
);

INSERT INTO test_dp
  ("firsttt", "secondd")
VALUES
  ('banana', '1'),
  ('apple', '1'),
  ('banana', '45'),
  ('banana', '1'),
  ('kiwi', '2'),
  ('grape', '2'),
  ('grape', '33'),
  ('strawberry', '56'),
  ('strawberry', '56');

Query

select 
  distinct(dp1.*) 
from 
  test_dp dp1 
  inner join test_dp dp2 on dp1.firsttt = dp2.firsttt
  and dp1.secondd <> dp2.secondd;

DB Fiddle UK