Q

Multiple distinct combinations

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

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close