Let's assume we have a table T with the following columns:

dc1  dc2  fc1  fc2  lc2
 1    1    11   12   21
 1    1    11   12   22
 1    1    11   12   23
 2    2    22   22   31
 3    3    33   35   32
 3    3    33   37   32

I want a SQL query that returns all the rows that satsfy the following criteria: dc1, dc2 has multiple distinct fc1, lc2 combinations (count > 1). For the above sample data, the SQL would return:

dc1  dc2  fc1  fc2  lc2
 1    1    11   12   21
 1    1    11   12   22
 1    1    11   12   23

    Requires Free Membership to View

    Login

The solution is straightforward and utilizes COUNT DISTINCT:

select T.* 
  from T
inner
  join (
       select dc1
            , dc2
         from T            
       group
           by dc1
            , dc2
       having count( distinct fc1,lc2 ) > 1
       ) as multiples
    on T.dc1 = multiples.dc1       
   and T.dc2 = multiples.dc2

Unfortunately, not every database system supports COUNT DISTINCT.

For those that don't, you can obtain the correct results, perhaps not as efficiently, with concatenation or some other expression to combine the two column values. For example, using Microsoft SQL Server syntax --

select T.* 
  from T
inner
  join (
       select dc1
            , dc2
         from T            
       group
           by dc1
            , dc2
       having count( distinct 
                   cast(fc1 as varchar)
                  +cast(lc2 as varchar) ) > 1
       ) as multiples
    on T.dc1 = multiples.dc1       
   and T.dc2 = multiples.dc2

This was first published in June 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top