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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close