Postgresql: select distinct - combinations of columns

Question

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.

wildplasser · Accepted Answer · 2013-03-20 18:55:39Z

5

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
  ;

answered Mar 20, 2013 at 18:55

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pmelch Over a year ago

It seems to work. And do I understand it right: When you have the smaller than sign one of the two rows are left out, because one of the two must have a value bigger than the other. Thats the idea right?

wildplasser Over a year ago

You want to find pairs with the same title. But the original query finds items that are part of such a pair. And for every pair there are two such members, so you can choose an arbitrary way of supressing one of the two pair-members, such as p1.id < p2.id

wildplasser Over a year ago

But you should realise that if there are more then two members of a same-title group, the result will still show more than one row (eg, for triplets: it will show {1,2} and {2,3} and {1,3})

Dhinakar · Accepted Answer · 2013-03-28 05:09:46Z

0

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

answered Mar 28, 2013 at 5:09

Dhinakar

4,1267 gold badges39 silver badges69 bronze badges

Comments

Max Power · Accepted Answer · 2018-05-31 11:30:34Z

0

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

answered May 31, 2018 at 11:30

Max Power

313 bronze badges

1 Comment

Ivan Over a year ago

Please add some explanation along with your code to help the OP solve her/his problem.

Collectives™ on Stack Overflow

Postgresql: select distinct - combinations of columns

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related