How can I count the number of unique pairs in a table that has a recursive relationship?

  • A+

Given the following table (people_table):

PersonId  CoupleId   -------------------    1        2      2        1      3        (null)      4        5      5        4   

By writing a query similar to this

Select count(?) from people_table 

I expect 2 as count result


I think the simplest method is:

select count(*) from people_table where PersonId < CoupleId  ; 

Why does this work? Well, two people in a couple have different ids. One of them must be smaller than the other and this counts one of them.

This also filters out NULL values.

Note: This assumes that your data is well-formed -- that is, both persons in a couple are in the table as separate rows.


:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: