I have a database that looks like this:
handshakes ----------------------------------- |id | participant1 | participant2 | ----------------------------------- | 1 | Thomas Miller| Max Miller | | 2 | Thomas Miller| Jack Miller | | 3 | Jack Miller | Max Miller | | 4 | Max Miller | Thomas Miller| | 5 | Jack Miller | Max Miller | -----------------------------------
It measures how many times participant1
and participant2
have shaked hands.
I want to select the amount of times total people have shaken hands (without the duplicates counted).
So in this example the output would be like this:
Thomas Miller | Max Miller Thomas Miller | Jack Miller Jack miller | Max Miller Total: 3times
Can anyone help me with the SQL Statement to do so?
Advertisement
Answer
With NOT EXISTS
:
select id, h.participant1, h.participant2 from handshakes h where not exists ( select 1 from handshakes where id < h.id and least(participant1, participant2) = least(h.participant1, h.participant2) and greatest(participant1, participant2) = greatest(h.participant1, h.participant2) )
This query uses the functions least()
and greatest()
which are supported by MySql and Postgresql.
See the demo.
Results:
| id | participant1 | participant2 | | --- | ------------- | ------------ | | 1 | Thomas Miller | Max Miller | | 2 | Thomas Miller | Jack Miller | | 3 | Jack Miller | Max Miller |