CODEWITHSUNDEEP
postgresqlselectdistinct-values
pmelch
pmelch

Reputation: 111

Postgresql: select distinct - combinations of columns

I have this query in PostgreSQL: 

select p1.id, p2.id, p1.title, p2.title 
from publication "p1", publication "p2"
where p1.title = p2.title and p1.id <> p2.id

The problem is that it returns more data than I need: 

id    id   title          title  
3456  5678 Ulysses        Ulysses  
5678  3456 Ulysses        Ulysses  
234,  345  Das Kapital    Das Kapital  
345   234  Das Kapital    Das Kapital

I only need rows 1 and 3, or rows 2 and 4.

Upvotes: 1

Views: 4333

Answers (3)

Max Power
Max Power

Reputation: 31

select DISTINCT p1.id, p2.id, p1.title, p2.title 
from publication "p1", publication "p2"
where p1.title = p2.title

Upvotes: 0

Dhinakar
Dhinakar

Reputation: 4151

I have simple idea to implement your scenario.

select p1.id, p2.id, p1.title, p2.title , sum(p1.id + p2.id) as temp
from publication "p1", publication "p2" group by temp

Upvotes: 0

wildplasser
wildplasser

Reputation: 44250

select p1.id, p2.id
 , p1.title, p2.title
from publication p1
    , publication p2
where p1.title = p2.title
  and p1.id < p2.id -- tie breaker
  ;

Or with the much hipper JOIN-syntax:

SELECT p1.id, p2.id
 , p1.title, p2.title
FROM publication p1
JOIN publication p2 ON p1.title = p2.title
                   AND p1.id < p2.id -- tie breaker
  ;

Upvotes: 5

Related Questions