Q
Problem solve Get help with specific problems with your technologies, process and projects.

COUNT DISTINCT revisited

Be careful using concatenation when COUNT DISTINCT is not available.

In your solution Multiple distinct combinations (23 June 2005), be careful using concatenation when COUNT DISTINCT...

is not available.

The distinct {fc1,lc2} sets {1,11} and {11,1} may not be distinct using simple concatenation and cast. If fc1 and lc2 are both numbers with known maximum allowed values, a calculation should work. For example, if both were limited to 3 digits (< 1000), then the expression (fc1 * 1000) + lc2 should work. Otherwise, it might be necessary to pad the first string out to its maximum possible length in the concatenation.

The above is a comment submitted by a reader, and I really loved it.

In my previous answer, I mentioned that some database systems don't support COUNT DISTINCT, and then suggested using concatenation (using SQL Server syntax for illustrative purposes) --

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

Thanks for the feedback, Steve. But you know what? I'm pretty sure that COUNT(DISTINCT fc1,lc2) is still going to see {11,1} and {1,11} as different!

Nevertheless, what really made my day was that somebody actually read my previous answer, thought about it, understood it, and took the time to write back. That's fantastic, and I do appreciate it.

This was last published in August 2005

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close